Mysql – Designing a scenario where a primary key could be of two types

database-designMySQLsubtypes

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:

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.
Create two completely different tables for the two types of users?
- This would result in redundant info.

Best Answer

enter image description here

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

Related Solutions

Should every table have a single-field surrogate/artificial primary key

I'm going to say no, not always, but most of the time yes..

These are some circumstances in which you don't need a surrogate or artificial key:

Pure intersection tables. If there is no risk of the intersection being the target of a foreign key and if there is little or no risk of the intersection attracting independent attributes (i.e. something other than FK's to the two parent tables) then you can get away with using the combination of FKs as the PK with fair confidence.
Lookup tables with static business keys. If you have a lookup
table with a unique business key which is fixed externally to your
business and which has zero chance of ever changing for any
practical purpose, then using the business key directly can make
things simpler. An example might be a list of state or province
codes or a list of ANSI standard numbers, etc.
Tables containing data consolidated from multiple, independent sources. If your system has many sources of data that must be shoehorned together into a single table, say at head office, then sometimes you need a compound key that includes the source system key value and a code indicating what the source system was.

There are also some situations where the old-faithful monotonically increasing integer surrogate key is not ideal. You can have keys that are alphanumeric surrogates. These could include:

Situations where you need to merge data from multiple, independent sources. To avoid key collisions you might use GUIDs instead of IDENTITY keys.
Situations where you are forced to use non-numeric key representations. Let's say you've got a license plate database. Your key could be the alphanumeric value instead of a pure number.
Situations where some external requirement forces you to apply compression to your key value. Instead of using 10 digits for an int32 you can use six base 36 digits.

Why most of the time yes? The most fundamental answer to that question is that it is pure hell if you ever need to modify a primary key value on any table. Since almost anything a user can see or touch is conceivably subject to an update at some point, using a visible key value is inviting pure hell. Using a surrogate key will keep you from falling into this trap.

Having said that, remember that there is room for YAGNI in applying this concept. You don't need to go forcing code tables with IDENTITY keys into every nook and cranny of your schema, just in case someone decides that the symbol for male gender in your employee table needs to change from M to X or something silly.

Mysql – SELECT for values in a table

SELECT x.id1, x.id2 
FROM   tbl x
WHERE  EXISTS (
    SELECT *
    FROM   tbl y
    WHERE  (y.id1, y.id2) = (x.id2, x.id1)
    );

Assuming that the combination of (id1, id2) is unique, so no DISTINCT. This would still allow (1,2) and (2,1) to coexist (and both will be returned), but no two rows with (1,2).

Also assuming id1 != id2 for every row (deriving from your test data and question). A unique entry (1,1) without doppelganger would be returned by this query. If my first assumption is true, you can exclude id1 = id2 right away. Else, you'd need something like row_number() to identify duplicates ...

A multicolumn index would greatly speed up such a query:

CREATE UNIQUE INDEX tbl_id1_id2_idx ON tbl (id1, id2);

Use it without UNIQUE keyword, should (id1, id2) not be unique after all.

Or, you can use those other techniques discussed under your prequel question, accordingly modified.

Best Answer

Related Solutions

Should every table have a single-field surrogate/artificial primary key

Mysql – SELECT for values in a table

Related Question