PK, IDs and simulating an 'object' in a table

Question

The object part is misleading. My question is not specific to one type of sql.

ATM i am using sqlite but i will be switching to TSQL (It looks to be what my host is offering) and i am rewriting some tables and logic to clean things up.

One pattern i notice is i have a bigint that could possible be one of 2+ keys and sometimes if i need it a bit or byte as an id to what type it is. Two major things that come to mind is 1) If a bigint is signed and i happen to have more then 2^32 PK in a table would bigint still be able to access the keys? I'm thinking since the value will be negative and PKs are always positive? that i will get an error. mistake, i forgot bigint is 2^63, i have nothing to worry about.

2) If i have a bigint that represents the PK of 2 or more tables would this be bad practice? For whatever reason i think there is a better way of doing bigint the_id, byte the_id

Answer 1

1) TSQL Bigint is not limited to 2^32, it is -2^63 to +2^63 -1 - far more than you are likely to use, something like 9.2 Quintillion.

2) Id prefer to not use an ID to represent two different PK's in other tables, but without knowing the problem you are solving it is hard to say whether it is the right decision or the only one you really have.

Answer 2

As a rule of thumb, I always design my columns to hold one piece of data and only one type of data (by type, I don't mean data type although that is generally true as well.)

If nothing else, putting two different IDs in the same column will prevent the use of foreign keys to make sure that your data is accurate and valid.