Hi Mr. Stephens. I have a quick question that I hope it makes sense... As far as I understand the Primary Key it's a constraint at the table level and it should be unique for that table. It will be strange to have the same *name* key as primary key for two tables?

users
-----
userID PK
...

drivingLicense
------------
userID (? PK & FK)
typeLicense
licensedSince

The "Users" table is bigger, but the other one it's that small and it seems to me that the primary key can be userID (drivingLicense.userID) that it is also the child of users.userID and should be used as Foreign Key as well. I guess it makes sense, but it feels a little strange.

Since I am here, I guess I have another one related to the "drivingLicense" table. Normally I guess it should be another table to contain the type of license, but how about instead of having two tables here we keep one table and since we have a finite number of options (from A to F) we define them as columns and input type will be date (since when that license, like B, was achieve) and we allow NULL that it will mean no license.

userID PK FK
A date null
B
C
D
E
F

Does this makes sense to you? Creating CRUD operations for the "drivingLicense" table it will be more difficult in the first example than the second example, I just want to check if it makes sense from the db point of view.

That is legal and makes sense in some cases. In your example, there is a one-to-one match between the Users and DrivingLicense tables and the key that connects them is UserId. In this case, I think it would be okay for the two tables to both have UserId fields that are primary keys.

I try not to use the same field name in two tables unless they mean exactly the same thing. For example, you could use Id as a field in the Users and Customers tables but really they mean two different things: a user's ID and a customer's ID. In that case, I try to differentiate the names (UserId and CustomerId) so I don't get confused when programming.

But in your example, I think it's okay.

I agree.

I think I would use UserId, LicenseType, and Date as fields; use UserId + LicenseType as the primary key; and UserId as a foreign key. You would have one record for each type of license that a user has.

For example, if my UserId is 1337 and I have class A and C licenses, then I would have two records: 1337/A and 1337/C.

(Making multiple fields like A through F that represent the same kind of information violates one of the normalization rules (I forget which one right now). Usually it's better to use separate records as I described. Then you can do things like select all licenses a user has (without needing to loop through a single record) and you can add more license types later if necessary.)

I hope that makes sense.

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)

So I should do it the proper way even if I found more advantages of having it done by breaking that normalization rule? Programatically I understand that I will have to make a loop to list all the types of driving license a person has.

On the other hand I have a join less. Being a job website employers are rather searching for employees with a specific type of license and this it's more precious than listing all the licenses a person has.

From this perspective I find that losing a join it's an advantage. At the code level it is easier to work with 1 table than with 2.

In this example, I may say that, A-F could be thought as atomic objects, without semantic value, in which case the rule mentioned would not apply.

Is this an "oops" or a big "wuhaaaaa" as I tend to favor the (bad) solution.

Thank you again, Mr. Stephens. Your answers are always thorough and clear and feels like you're explaining it over a coffee. Like it was not enough that the book is great...

You have to make the decision. If you think it'll be less work as one record and you don't think you'll have to modify the table much (i.e. the government won't add or remove license classes), then you may want to go with the denormalized version.

But don't come crying to me when they create a new "G" class license for driving flying cars that carry more than 6 passengers!

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)

Sir, I'll have to come to you as I trust your judgement and it is unlikely to find a better teacher than you are.

However, in the situation you mentioned I will simply add a new column "G".