Wrox Programmer Forums

Need to download code?

View our list of code downloads.

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
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 13th, 2012, 03:34 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 PK and FK

I have several tables like education and experience (all that can be found in a CV) that depend on the id key. Is this a bad design? If the id is the primary key everywhere there will not be possible to apply a foreign key constraint. Until reading your book I always made up a primary key for each table, than I was simply made the id the foreign key. Now I try to find natural keys as per your advise, however for these particular tables I cannot find something to qualify. I am wondering if the design is good or not. Also if there are circumstances (like this one) when making up a foreign key will actually help (in my mind as beginner). And third, I am wondering if dates (like registration date) can be used as primary keys in association with something else. I know it is a stretch, but it will be educative to understand why datetime stamps does not cut for the primary keys.

Code:
users
-----
id (PK)

education
---------------
id (PK)

experience
------------
id (PK)
Thank you again for your answers, appreciate your time and professionalism.
Reply With Quote
  #2 (permalink)  
Old April 13th, 2012, 04:09 PM
Rod Stephens's Avatar
Wrox Author
Points: 2,999, Level: 22
Points: 2,999, Level: 22 Points: 2,999, Level: 22 Points: 2,999, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 601
Thanks: 1
Thanked 84 Times in 83 Posts
Default

I'm not sure I quite understand which part of this is causing the confusion.

It should be possible in every table to define some primary key. If you can't, that probably means the table contains non-unique values and that violates data normalization constraints.

But that doesn't mean you should define a separate ID field to be used as an ID field. For example, suppose you have a User table with a UserId field. You have a Skill table that has a UserId field to tell you which user owns the record and a "Skill" field that gives one of the person's skills (Database Design, Mathematics, Windows OS, etc.).

The combination of UserId and Skill is unique because you shouldn't list the same skill twice for the same user. UserId/Skill could be a primary key but do you really need it?

Some database systems require a primary key so they can figure out how to update and delete records (so you may need to create that key if the DB requires it) but your program may never need that combination. Instead your program will probably just select all Skill records with a particular UserId so UserId would make a good index. It would also probably make a good foreign key constraint so you cannot create a Skill record unless there is a corresponding User.

It also doesn't hurt too much to make the primary key. It will slow updates, insertions, and deletions slightly and take up a little more space but it probably won't hurt anything. And it will enforce uniqueness (which is a good idea) so you may want to make the UserId/Skill combination a primary key just to cover all the bases.

Quote:
Now I try to find natural keys as per your advise, however for these particular tables I cannot find something to qualify.
If you can't find a candidate that requires uniqueness, then there's probably something wrong with your design. In general a table should not be allowed to contain two exactly identical records.

Quote:
Also if there are circumstances (like this one) when making up a foreign key will actually help (in my mind as beginner).
Foreign keys help protect the database from holding invalid data like a Skill record that doesn't correspond to any User. (You could also define a Skills lookup table for the Skill table. It would list the allowed skills that the Skill table could hold. That would also be a foreign key constraint. It would, for example, prevent the user from accidentally entering Debuging as a skill when it should be spelled Debugging.)

Quote:
And third, I am wondering if dates (like registration date) can be used as primary keys in association with something else. I know it is a stretch, but it will be educative to understand why datetime stamps does not cut for the primary keys.
Dates are usually not used for primary keys for a couple of reasons. One is that on a large database, eventually you will create two new records at roughly the same time and they will try to use the same timestamp. If you record the time to the millisecond, you reduce the chances of a collision but you also save some information that probably isn't very useful. (And some day you will still get5 a collision, just not soon.)

Some companies use part of a timestamp plus other information. For example, they might use the date plus the user's initials. They may also add on a random number to handle collisions when two people with the same initials open accounts on the same day.

All of these methods are basically creating some arbitrary value to represent users. You can invent systems to try to make them unique but most databases already provide auto-number fields that can automatically assign unique numbers to records anyway so why not use that feature instead? It's easy, guaranteed to be unique, and produces a simple number instead of a complicated value that includes date and time information.

If you want to record the date or time when a record was created, you can also save that in a separate field. Then you can store the data you want (perhaps just the date) and not the timestamp to the millisecond. (Of course you can save the timestamp to the millisecond if you want and some databases may be able to do that automatically so you may as well if you want that data available.)

If you do work with dates, you may also want to consider the format in which you save it. For example, you may want to save dates and times in UTC so you get a true time no matter what part of the world you're in. For example, then a record creates at 9:00am in Los Angeles won't look like it was created before a record created at 10:00am in London.

Many database engines automatically saves dates in UTC and you need to figure out how to convert to the format you want to display on the screen so it may not be an issue.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #3 (permalink)  
Old April 13th, 2012, 05:39 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 users-userskills-skills, something doesn't work as it should...

I am not sure if I understand correctly.

I understand the part where it should be a lookup table "skills" to make a list of all allowed entries. To make it happen I have to link what I marked with (Y) together. So "skills" will have the foreign key "skill" that references "userskills" on column "skill".

Now what you are saying is that I may never need "skill" column in "skills" as primary key, because I will always refer to "useskills" to retrive the skills associated with an user. However, just to be clear, as all tables need a primary key, even if I do not make use of that table in the application's code, I still have to make that column primary key because all tables must have a primary key. Is this correct or your suggestion is to leave it as FK only?

Next, if I understand correctly, I do not need an artificial primary key because I can use the combination of "id" and "skill" in "userskills" as compound key guaranteeing this way uniqueness. Am I following you correctly until now?

Next, I should make the link between what I marked with (X) between "users" and "userskills". The intention is to constrain the "id" from "userskills" to accept values only if they are in "id" column in "users" table. If you are not a user you cannot have any skills. I cannot make the "id" in "users" table foreign key referencing the "id" in "userskills". What kind of constrain can be used here? This is what I do not understand at all. Every time it gave me an error (950-6). Is this a bug or I am doing something wrong? I am using MySQL 5.5.16 on Windows (XAMPP).


Code:
users
-----
id (PK) X
                   
userskills       
---------      
id (PK1) X
skill (PK2) Y
                       
skills                
-----                
skill (PK) (FK) Y

Last edited by masterlayouts; April 13th, 2012 at 05:43 PM.
Reply With Quote
  #4 (permalink)  
Old April 14th, 2012, 11:05 AM
Rod Stephens's Avatar
Wrox Author
Points: 2,999, Level: 22
Points: 2,999, Level: 22 Points: 2,999, Level: 22 Points: 2,999, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 601
Thanks: 1
Thanked 84 Times in 83 Posts
Default

Quote:
I understand the part where it should be a lookup table "skills" to make a list of all allowed entries. To make it happen I have to link what I marked with (Y) together. So "skills" will have the foreign key "skill" that references "userskills" on column "skill".
I think it's the other way around. UserSkills.Skill should be a foreign key constraint with Skills.Skill. In other words, all UserSkills.Skill values must exist in the Skills table. Skills.Skill is the parent field and UserSkills.Skill is the child field.

Quote:
Now what you are saying is that I may never need "skill" column in "skills" as primary key, because I will always refer to "useskills" to retrive the skills associated with an user.
The Skills table should have Skill as its primary key to enforce uniqueness. Also the DB may require it to be a primary key if you are going to use it as the parent of a foreign key constraint.

Your program will probably never look up anything in the Skills table. It is just a lookup table used to require that the UserSkills.Skill values are from the allowed list.

Quote:
However, just to be clear, as all tables need a primary key, even if I do not make use of that table in the application's code, I still have to make that column primary key because all tables must have a primary key. Is this correct or your suggestion is to leave it as FK only?
Usually all tables should have a primary key. There should be a combination of fields that can act as the key. Otherwise the records in the table would not be unique and that violates normalization rules.

Some DB engines require a primary key if you will ever update or delete records in the table.

Quote:
Next, if I understand correctly, I do not need an artificial primary key because I can use the combination of "id" and "skill" in "userskills" as compound key guaranteeing this way uniqueness. Am I following you correctly until now?
Yes. The primary key doesn't need to be a single field. It can be a combination of fields such as ID + Skill in the UserSkills table.

Quote:
Next, I should make the link between what I marked with (X) between "users" and "userskills". The intention is to constrain the "id" from "userskills" to accept values only if they are in "id" column in "users" table. If you are not a user you cannot have any skills.
That's correct.

Quote:
I cannot make the "id" in "users" table foreign key referencing the "id" in "userskills". What kind of constrain can be used here?
I think you're making this one backwards. UserSkills.Id should be a foreign key constraint referring to Users.Id. The Users.Id field is the parent field and UserSkills.Id is the child field. Then you should not be able to use a value in UserSkills.Id that is not already in Users.Id.

(I would also use a more descriptive field name like UserId instead of just Id. It's a bit more verbose but having lots of tables all with Id fields can be confusing.)
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

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


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
Database structure PK - FK robbaralla BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 7 January 15th, 2010 12:05 PM
Null and FK scandalous SQL Server 2005 1 March 28th, 2007 03:40 PM
setting the value of FK csharpa SQL Server 2005 1 March 8th, 2007 12:50 PM
setting the value of FK csharpa .NET Framework 2.0 0 February 28th, 2007 09:53 PM
setting the value of FK csharpa ADO.NET 0 February 28th, 2007 09:50 PM



All times are GMT -4. The time now is 02:12 PM.


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