Quote:
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?
|
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.
Quote:
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.
|
I agree.
Quote:
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.
|
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.