Nullable Foreign Keys

Hi,

I'm making a system enhancement and have a database design Q.

The database contains a transactionitem table with a foreign key relationship to a Product Table. I need to extend the database schema to show that the TransactionItem.Product may also relate to another type of product grouping (depends on the sale type and this will be solely for reporting). Note. I can't link back using the TransactionItem.ProductID as this could belong to more than one group.

My Question really is "Is it Good practise to have foreign key column with null values". I estimate that 80% or transactionitem records would contain null for this colunm. If I use an intermediate table the relationship between my TransactionItem and my new (new) ProductGroup table would be 0 to one. I.E : this product is only sold in special circumstances - and although the product itself can belong to more than one group for this specific sale it only applies to one group.

Should I add a column to the TransactionItemTable as below.

Table - TransactionItem

TransactionItemID ProductID ProductVoucherID

1 500 2

tblProductVoucher

ProductVoucherID (Other attributes)

2 .......

- OR Create a new association table

Table - TransactionItem

TransactionItemID ProductID

1 500

Table - TransactionVoucherItem

TransactionVoucherItemID TransactionItem ProductVoucherID

1 1 2

tblProductVoucher

ProductVoucherID (Other attributes)

2 ......

Can you clarify one part about your second option? Wouldn't TransactionVoucherItem inherit its primary key from TransactionItem? i.e.it doesn't need its own TransactionVoucherItemId, does it? Rows in this table exist 1:1 with TransactionItem's when that transaction has an associated ProductVoucher it sounds like.

To answer your overall question, I'd look at how often queries that select TransactionItem's need to also know if there is an associated ProductVoucher. If you go the separate table route, then look at how many queries over TransactionItem now have to be 'left joined' with TransactionVoucherItem in order to pickup the ProductVoucherID column. If there are a lot of queries that require this, then it will probably be cheaper to use your first option.

Also, to help decide, look at the current rowsize of a TransactionItem and see what percentage increase it would be to add the nullable ProductVoucherID column. If this is your biggest table, and this increase is more than say 20% of the current rowsize, that increase might outweigh the cost of the joins mentioned above. (I think this is the basic tradeoff from a performance perspective.)

From a logical modeling perspective, I've used the optional foreign key approach (your option 1) before without any issues for my database. It's probably the first option I'd consider as its the simplest, but you'll have to measure to see if it has any negative tradeoffs (like the extra size mentioned above.)

HTH.

--Shaun

|||

>>My Question really is "Is it Good practise to have foreign key column with null values". I estimate that 80% or transactionitem records would contain null for this colunm. If I use an intermediate table the relationship between my TransactionItem and my new (new) ProductGroup table would be 0 to one. I.E : this product is only sold in special circumstances - and although the product itself can belong to more than one group for this specific sale it only applies to one group.<<<

To me this is not a matter of how many mihgt be null, but what the relationship between them is. If you have a one to many relationship between the grou and the transaciton item, then that would be the best route. However, if there is any possibility that you might have multiples, then the seperate table is the best approach. Then you could have >1 group per transaction.

Having a column being 80% null won't be too much of an issue, especially since you are talking about using this as a lookup value to display and group by rather than as something to search for. You could even add a value for does not apply and make it not-nullable, especially if you will have to do a coalesce on all of your reports later and say coalesce(reportGroup.name,'Does not apply')

|||How if the foreign key references the primary key? Can it still have null in value? I am encountering a similar problem with my database.