March 8, 2011 at 5:59 am
I am a newbie when it comes to SQL. I am using Microsoft's SQL server 2008 r2. I'm not the greatest with the SQL scripting, funny enough if this was C++ or C# its would be a lot easier for me. I many use the Gui for most of my Database building. I have run into a problem with foreign keys, at least I believe its with foreign keys. I have done a few Google searches and searched the forums here. I haven't turned up much on it some things were close but not quite. I may have even stumbled upon the answer but didn't know what I was looking at. To keep things as simple as possible, mainly for me. Lets say I have a database named Database and two tables named Jobs and Codes. In the Jobs table I have 5 Columns Index_Jobs, Name, Code1, Code2, and Code3. In the Codes table I have 2 Columns Index_Codes and Description. I know how to set up a foreign key so that Code1 in Jobs table is referenced to Index_Codes, so that when I set up a query It displays the Jobs table with the Description referenced by Code1, however I can not seem to find a solution to getting it to do the same for Code2 and Code3 other than creating 2 more tables that would have the same exact data as Code1. Any help would be greatly appreciated, Thank you.
March 8, 2011 at 6:44 am
The reason you're having trouble with that is that Foreign Keys aren't built to work that way.
What you've done, by having Code1, Code2, Code3 is violate First Normal Form for relational databases. You might need to do that, and you can certainly get away with it if you have to, but the database engine isn't built to support that. (It's the same reason that you won't be protected by airbags if you sit on the roof of a car. You can do it, but the manufacturer is kind of assuming that it's usually not a good idea.)
You can still query the data the way you want, it just won't be "protected" by foreign keys. You can use triggers to enforce the things that foreign keys usually handle.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 8, 2011 at 7:26 am
Jamesyoung79 (3/8/2011)
I am a newbie when it comes to SQL. I am using Microsoft's SQL server 2008 r2. I'm not the greatest with the SQL scripting, funny enough if this was C++ or C# its would be a lot easier for me. I many use the Gui for most of my Database building. I have run into a problem with foreign keys, at least I believe its with foreign keys. I have done a few Google searches and searched the forums here. I haven't turned up much on it some things were close but not quite. I may have even stumbled upon the answer but didn't know what I was looking at. To keep things as simple as possible, mainly for me. Lets say I have a database named Database and two tables named Jobs and Codes. In the Jobs table I have 5 Columns Index_Jobs, Name, Code1, Code2, and Code3. In the Codes table I have 2 Columns Index_Codes and Description. I know how to set up a foreign key so that Code1 in Jobs table is referenced to Index_Codes, so that when I set up a query It displays the Jobs table with the Description referenced by Code1, however I can not seem to find a solution to getting it to do the same for Code2 and Code3 other than creating 2 more tables that would have the same exact data as Code1. Any help would be greatly appreciated, Thank you.
Funny to find a question about FKs that apparently is not related to enforcing Referential Integrity - which is what FKs are for.
I agree with previous poster on design issue - why don't go to 3NF?
Like...
JobsTable
IndexJobs
Name
JobsCodesTable
IndexJobs
Code
CodesTable
Code
Description
Relationships should look like...
JobsTable <=(IndexJobs)=>> JobsCodesTable <<=(Code)=> CodesTable
The right way to do it is to start with a ER model then translate it for physical implementation at least at Third Normal Form a.k.a. 3NF
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 8, 2011 at 7:27 am
I got a chuckle out of the air bag analogy. The reason I was wanting it was asking is I already have an editor built in C# for it that allows me to browse though Data base entries and enter in new ones that displays the descriptions for the codes. I like the protection of the foreign keys, but then again its not likely my program will enter in a code that doesn't exist, I say likely because who knows what may happen with code modifications or corrupted data transfers in the future. I like the convenience of being able to do a query and seeing descriptions rather than numbers for stuff. Do you have a suggestion on how I could go about doing that?
March 8, 2011 at 7:34 am
Jamesyoung79 (3/8/2011)
... I like the protection of the foreign keys, but then again its not likely my program will enter in a code that doesn't exist...
Never underestimate the capabilities of the user community - if something is possible sooner or later they will do it.
One of the core competences of the DBA is to protect the data, DBA are the last line of defense therefore no effort should be spared to be sure data is safe.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 8, 2011 at 7:40 am
Funny to find a question about FKs that apparently is not related to enforcing Referential Integrity - which is what FKs are for.
I agree with previous poster on design issue - why don't go to 3NF?
Well I'm just apparently tring to shove a round peg into a square hole getting it halfway though and getting pissed it does go through the rest of the way, So I'm looking for a hammer or a saw.
Like...
JobsTable
IndexJobs
Name
JobsCodesTable
IndexJobs
Code
CodesTable
Code
Description
Relationships should look like...
JobsTable <=(IndexJobs)=>> JobsCodesTable <<=(Code)=> CodesTable
I'm not seeing what your getting out why would JobsCodesTable be necessary? I'm trying to assign up to 3 codes off of the master list to an entry in Jobs.
The right way to do it is to start with a ER model then translate it for physical implementation at least at Third Normal Form a.k.a. 3NF
Hope this helps.
I'm going too see what I can find on ER model and 3NF to see what I can find on it and if you'd care to explain a little or point me in the right direction that would be appreciated as well 😀 . Thank you for your reply and help.
March 8, 2011 at 7:56 am
I can infer from your initial post that:
1 Job can have 1 or many codes
1 Code can can belong to 1 or many jobs.
This is an example of a many to many relationship that needs to be decomposed into a series of 1 to many relationships
To accomodate this, you need to use the JobCodesTable (Google junction or link tables) demonstrated by PaulB. This will then afford you the protection of the foreign key relationships (as well as a whole host of other things).
You would then query as follows
SELECT j.Name,c.Description
FROM JobsCodesTable jc
INNER JOIN JobsTable j
ON j.IndexJobs = jc.IndexJobs
INNER JOIN CodesTable c
ON jc.Code = c.Code
March 8, 2011 at 8:11 am
Jamesyoung79 (3/8/2011)
I'm going too see what I can find on ER model and 3NF to see what I can find on it and if you'd care to explain a little or point me in the right direction that would be appreciated as well.
Bing "data modeling", "normal forms" and look for 1NF, 2NF and 3NF - for now stop right there.
As a rule of thumbs anything can be resolved at the 3NF -Third Normal Form.
"3NF" is to the DBA like the "period" is to the COBOL programmer or the "dog" is to the men - the best friend 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 8, 2011 at 8:39 am
Thank you all very much for all the help I will look that stuff up. It makes finding a solution to my problem a whole lot easier now that I know what the question is.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply