CODEWITHSUNDEEP
sql
ooo
ooo

Reputation: 3

What is the best practice for having a unique Id for all tables used by an application?

Let's say I have an application that runs and stores its objects in 3 different tables (A, B and C).Visual context

What I want to do is : - To keep the efficiency of an int PK for sorting and searching the tables. - When a customer calls the customer service by giving it's ID being able to identify if that ID comes from table A, B or C.

Here are the potential solutions I came up with :

  1. Have a local id (for the table) that is an int and a global id (for the public) that is local_id + A, B or C as a suffix. So 10C is for the id 10 in table C.
  2. PK of table A starts from 1 million; table B => 2 millions;...
  3. Have a master table that generates ID and identify which ID is for which table.
  4. All the ids in table A end with 00, in B with 01, in C with 02,... (not sure if it's faisable?!)

Other options looked at : 3. UUID and GID don't answer the need since I won't be able to know in which table the ID goes.

My favorite is option 1 since it's not heavy to implement and scalable.

And you what would you do?

EDIT : Please consider that columns in A, B and C are completely different. If it wasn't the case I wouldn't be asking the question.

Upvotes: 0

Views: 1912

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12959

I would suggest option 2 little differently. I would suggest you to keep three integer sequences: Seq_A, Seq_B, Seq_C. These sequences will have different ranges:

  • Seq_A : 1 to 1,000,000,000
  • Seq_B : 1,000,000,001 to 2,147,483,647
  • Seq_C : -1 to -2,147,483,648

Have the logic in such a way: The Id should concatenation of: TableName + ID.

  • If the table name is C, go for the negative ID in Table C
  • If the table name is A and ID < 1 Billion , go for Table A
  • If the table name is B and ID > 1 Billion , go for Table B

Upvotes: 1

daShier
daShier

Reputation: 2116

The best practice is not to organize your data this way. You should not have three tables with unique primary keys. This will lead to lots of trouble, not the least of which is concurrency issues.

You didn't explain why you have three tables that seem to have similar data, but there should be much better solutions: Perhaps having all the records in one table with a column that indicates data A, B, or C? Explain your application in more detail if you want a better suggestion.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

Actually, a UUID combined with a junction table which relates the ID to the table in which it appears is one option here. The definition for this junction table might look like:

CREATE TABLE id_map(id binary(16), table_name varchar(36));

But note that being able to get the table name from the above given a UUID won't be too useful when writing queries. A more relational way of doing this would be to setup primary foreign key relationships between the A, B, and C tables. Also, it might actually make more sense here to just have a single table, with a single ID column. Whether you do that depends on whether the structure of the three tables be very similar or not.

Upvotes: 0

Related Questions