| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2005-11-24 : 13:52:21
|
| Hi friendswe need ur advice on a foreign key issue. we have a parent table and a child table.here fkey is not mandatory so it can be null.this child table may contain no of rows without a fkey.question is we have a new member in our team and he says that using nulls there can be performance overhead and suggests that instead of having a null we can create a new parent (only 1) record and use that key to store in fkey. i mean a sample like followingparentTab ChildTab-------------- ---------------pkey name fkey,price---------------------------------1 dave 1 , $122 emy 2 , $133 for null records 3 , $12 3 , $10 (here 3 means a dummy parent)is it good design ? whats the advantages and dis advantages?my intial thought its not correct as we r unnecessarily taking up lot of space. i mean if fkey is a integer and as you know it takes 4 bytes and my table has 5000 records without any fkey then we'd be wasting space of 5000*4 !! am i right on this one.Thanks for your help on this .Cheers |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-25 : 06:23:34
|
| "using nulls there can be performance overhead."...cobblers! They are more 'particular' to use but not a slowdown factor.re space saving....unless you are saving your data onto a 1mb floppy, why care about 20k when you could have a databse of 20GB.The premise you should be worried about is modelling the relationships between the data items correctly....everything else should flow natually and easily from the proper data model. |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-11-26 : 14:57:40
|
| actually i was not worried abt 20k Andrew,i was only giving an example. I think having dummy fkey is not a good idea for e.g INNER JOIN on this FK.Null column will eliminate absent joins before it's selected.Dummy "Instead of NULL" value will make SQL select all records and then to filter out those not to be actually selected.actually i need some good points when i explain these to our new guy why its not good.Cheers |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-26 : 15:32:06
|
| As Andrew mentioned, you should be worried about a design that allows a NULL FK. Very poor indeed. I am sorry but you have zero ammunition against this other guy..DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-27 : 01:43:35
|
| "I am sorry but you have zero ammunition against this other guy"You'd have a dummy value instead of NULL values? Or you'd redesign it to have real values and no NULLs?'Coz I think its the first that the "other guy" is recommending ...Kristen |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-27 : 04:40:28
|
| >> Or you'd redesign it to have real values and no NULLs?Bingo!DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-27 : 07:48:07
|
Yup, agree with that.So my 1,2,3 order of "preference" is:1) Redesign so there are no NULL valuesmaybe) Allow NULLsnever ) Have a Dummy Value to "make" the FKKristen |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-11-27 : 14:54:34
|
| Thank you all for your replies. Actually i wanted see if there is any real value in our new guy's advise so i thought if i post here i get some expert advise whether its good or not and whats upside and downsides.so far ,if you see , all previous posts no one actually gave a real point whether its good or not and appreciate if you can be more specific why its good or bad.Thanks again.Cheers |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-27 : 15:07:30
|
| As you noticed "no one actually gave a real point" because the whole issue should not arise in the first place.Let's say that it is how it is... (Props to Arisotle!)What does a NULL mean in this case?DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-11-27 : 15:47:04
|
| I totally agree with you "byrmol" that the whole issue should not arise in the first place.my question still remains why it should not arise ,whats technical reasons!! is there really noticeable peformance overhead using nulls !!anyway the answer to ur question is ..>>What does a NULL mean in this case?there is no linking parent for this child record.Cheers |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-27 : 16:47:08
|
| It's not the performance at all.It is the fact that the relations (table) proposition has been utterly destroyed.The main purpose of FK's is to ensure that a "child" cannot be "parentless"."Who's your daddy?""I don't have one""Therefore you don't exist!"DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
|
dummy foreign key ?