Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table: all_info, where it holds the users identification. This id can be of two types (just one or another, never both), suppose: int and varchar, but one table can't have two primary keys, and a composite one wouldn't solve my problem.

So, i can't do this:

+--------------------+
| all_info           |
+--------------------+
| PK id1 varchar(50) |
| PK id2 int         |
| ...                |
+--------------------+

Then, i created two others tables: unique_info1 and unique_info2 with the primary keys of the types that i needed, add some informations for the specific types of users, and made relations with the table: all_info, that holds the rest of the users informations (that both types share).

With this scheme, i could relate unique_info1 and unique_info2 with every other table, but i would need to create two columns in every one of them for make that relation. To solve this, in all_info i had created an artificial primary key to make all posterior relations.

Now, it looks like this:

Obs: FK uniq1_id varchar(50) and FK uniq2_id int(10) are unique and nullable.

+--------------------+    +--------------------+    +-------------------------+    +-------------------------+
| unique_info1       |    | unique_info2       |    | all_info                |    | other_table             |
+--------------------+    +--------------------+    +-------------------------+    +-------------------------+
| PK id varchar(50)  |    | PK id int(10)      |    | PK id int(10)           |    | ...                     |
| ...                |    | ...                |    | ...                     |    | ...                     |
| ...                |    | ...                |    | FK uniq1_id varchar(50) |    | ...                     |
| ...                |    | ...                |    | FK uniq2_id int(10)     |    | FK all_id int(10)       |
+--------------------+    +--------------------+    +-------------------------+    +-------------------------+

The thing is: that's the best approach, or should i change the plan?

For examples:

  1. Choose other information to be the id of the users, where all will have the same type, and add all specific info of the users to that hole table?

    • This would result in a lot of null fields for each user.
  2. Create two completely different tables for the two types of users?

    • This would result in redundant info.
share|improve this question
1  
Your question is really confusing. Are you saying that there are two types of users? If so, which columns are common and which are specific to each type? Or, do both types have id_1 and id_2? –  Damir Sudarevic Oct 9 '13 at 12:33
    
They have just one or another. Never both. I will edit the question to be more clear. –  Rafael Barros Oct 9 '13 at 14:10
1  
I question whether vertical partitioning makes sense here. Few applications will have millions of users, so I doubt there's a strong performance reason. You're creating some real complexity and room for inconsistent data this way; are you sure it's necessary? NULL fields are not evil. –  Jon of All Trades Oct 9 '13 at 15:17
    
@JonofAllTrades - No, i'm not sure. So, you would vote for my first example, where i have to choose other info to be the pk and have a unique table for all users? –  Rafael Barros Oct 9 '13 at 16:03
1  
Yes, that's what I'd do. A synthetic ID rarely hurts, and in this case it's significantly simpler. I'm a little confused about exactly what you're asking - it sounds like you really want opinions on whether to partition this table, and it's not really about PKs. If so, then I think @david25272's answer is right on. –  Jon of All Trades Oct 9 '13 at 16:07
add comment

2 Answers 2

up vote 1 down vote accepted

enter image description here


Also search for super type/subtype here and on the SO.

share|improve this answer
    
I'll have to study this diagram, after i'll give you feedback. Thanks. Just a detail: your word "here" was meant to be a link? –  Rafael Barros Oct 9 '13 at 16:41
1  
@RafaelBarros; here on DBA.SE site. –  Damir Sudarevic Oct 9 '13 at 17:01
    
Oh, ok. I've been thinking and looks like the things that i haven't understood on the diagram doesn't influence the big picture of the idea. It looks like your way is much more obvious, and that i was thinking upside down. Thank you again. –  Rafael Barros Oct 9 '13 at 20:19
add comment

If all users have both types of ID you could make id2 the primary key, and build a unique not null index for id1 (assuming that both types of ID have to be unique). Only id2 could be used as a foreign key in this scheme, but you could always retrieve id1 with a simple join.

If not all users have both types of ID you could make two unique (but nullable) indexes, and add a third column to be the primary key (something like id3 integer identity primary key might be suitable). You would then use id3 in other tables as a foreign key.

share|improve this answer
    
Your second idea corresponds to my scheme. And that is what is already implemented: FK uniq1_id varchar(50) and FK uniq2_id int(10) are unique and nullable; your id3 integer identity primary key is my PK id int(10) [AI]. I will edit the question to be more clear. So, i suppose you agree with my design? –  Rafael Barros Oct 9 '13 at 14:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.