Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 2nd, 2012, 01:11 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default Same name as PK in many tables, any problem with that?

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.
  #2 (permalink)  
Old May 2nd, 2012, 01:25 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,153, Level: 23
Points: 3,153, Level: 23 Points: 3,153, Level: 23 Points: 3,153, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 643
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
The Following User Says Thank You to Rod Stephens For This Useful Post:
masterlayouts (May 2nd, 2012)
  #3 (permalink)  
Old May 2nd, 2012, 02:12 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default if we think them as non-semantic atomic values?

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...
  #4 (permalink)  
Old May 2nd, 2012, 02:21 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,153, Level: 23
Points: 3,153, Level: 23 Points: 3,153, Level: 23 Points: 3,153, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 643
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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!)
  #5 (permalink)  
Old May 2nd, 2012, 02:46 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default

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".
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
PK and FK masterlayouts BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 3 April 14th, 2012 11:05 AM
Need help! Setting PK value at runtime! Darrell86 SQL Server 2005 6 November 20th, 2006 12:33 AM
importing from flat file PK problem olambe Classic ASP Databases 3 October 18th, 2004 04:09 PM
PK problem reindeerw Access 10 April 15th, 2004 01:26 AM



All times are GMT -4. The time now is 05:37 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.