April 16, 2009 at 1:37 pm
When should it be appropriate (if at all), to store text instead of a foreign key?
I have a table where it could be a FK in 4+ tables and sometimes I think that it would make doing queries easier if it were just text.
Here's my example:
===================================================
ProgramList (contains the PK ProgramListID and ProgramName)
ReferralData (should I store the ID as a FK or just the ProgramName?)
Staff (do I store the ID as FK or the ProgramName)
JobCredit (same thing)
There can also be multiple tables for each unique program offered where there could be another FK/text relationship.
====================================================
If I go w/the FK option I will need constraints to make sure records are not orphaned and are validated by being selected from the ProgramList table.
What are the thoughts on this?
Am I over thinking it or trying to do something that's not good practice?
Thanks!
April 16, 2009 at 2:25 pm
These should be foreign keys. Think of hard it will be if you store the program name in multiple tables and the name of the program changes. :w00t: What if there are multiple programs with the same name? How do you know which one?
This is really the essence of relational data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2009 at 2:51 pm
That's what I was thought.
I'm moving stuff over from Access to SQL and I'm cleaning things up. One of those things is converting that stuff to keys (which I've already done).
It just looks odd in the relationships window and I hope that it's done correctly and it is ok for SQL. That ProgramList table really spiderwebs out with it's relationships to those other tables.
Is that normal to have one table having a bunch of those types of relationships or should some of those be created in views?
I'm guessing not because then you will lose out on the referential integrity.
April 16, 2009 at 2:55 pm
Yes it is very normal (even normalized :-P) to have several foreign key relationships to any given table. If you have time I would defintely recommend catching up on some reading around relational database design. It sounds like you are on the right track. Good luck!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2009 at 3:41 pm
Hi
jbloes (4/16/2009)
If I go w/the FK option I will need constraints to make sure records are not orphaned and are validated by being selected from the ProgramList table.
This is the main (only) reason for FKs. The constraint ensures that no program will be removed and leave orphaned links in other tables. If your programs become delete sometimes you can use ON DELETE CASCADE to remove all related data.
Greets
Flo
April 16, 2009 at 4:41 pm
Absolutely use the FK's. They protect the data.
I'm not even sure what you mean by "storing it as text." Do you mean that you'd just record what the relationships should be and then other than that, let the data go where it might? I wouldn't recommend that approach at all. Cleaning up orphaned data can become a career and it's not one you want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply