November 24, 2005 at 6:35 pm
Hi friends
we 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 following
parentTab ChildTab
-------------- ---------------
pkey name fkey,price
---------------------------------
1 dave 1 , $12
2 emy 2 , $13
3 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 .
November 24, 2005 at 8:44 pm
Keep your approach and get this "NOT NULL" guy away from DB design.
4 bytes is not only the issue.
Imagine 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.
And there are some other issues to point, if you are really interested, we could discuss it later.
P.S. Don't believe anybody who have read on article with wrong points in it without understanding of the relational database principles.
_____________
Code for TallyGenerator
November 24, 2005 at 9:26 pm
Thanks for the reply sergiy and yes u r right. actually i need some good points when i explain these to our new guy why its not good.
November 28, 2005 at 2:03 pm
Could you provide a little more information about what you are trying to do? I'm not clear on what the business rules are.
It sounds as though your business rules allow records in the 'child' table that have no relationship to a record in the parent table.
If you allow NULL values in the foreign key field of a child table, you cannot enforce referential integrity. This basically means that you do not have a parent-child relationship.
If this is the result you want, I don't see any value in a dummy 'parent' record. If you do want a parent-child relationship, you will need to change the design anyway to disallow nulls in the foreign key field and to require a matching record exist in the parent table before a record can be added to the child table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply