2

I have a table: all_info, where it holds the users identification. The id of this table 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 to establish that relationship. To solve this, I had created an artificial primary key in all_info to make all posterior relationships.

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 columns for each user.

  2. Create two completely different tables for the two types of users?

    • This would result in redundant info.
Improve this question
5
  • 2
    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, 2013 at 12:33
  • They have just one or another. Never both. I will edit the question to be more clear.
    – Rafael Barros
    Oct 9, 2013 at 14:10
  • 2
    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, 2013 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, 2013 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, 2013 at 16:07

2 Answers 2

Reset to default
3

enter image description here

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

Improve this answer
5
  • 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, 2013 at 16:41
  • 2
    @RafaelBarros; here on DBA.SE site.
    – Damir Sudarevic
    Oct 9, 2013 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, 2013 at 20:19
  • @DamirSudarevic How about the ERD (Entity Relationship Diagram) ? I have case like this, but I don't understand how to make its ERD? Could your tell me please?
    – Ayuktia
    Sep 7, 2016 at 18:36
  • @Ayuktia It's probably too late now, but you may find this answer of help in that regard.
    – MDCCL
    May 3, 2018 at 14:15
1

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.

Improve this answer
1
  • 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, 2013 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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