Relationship constraints

Hi,

I am trying to get the best solution for my problem:

I have 2 tables. Customer and Transaction. A transaction can contain a customer but not always. As I see it I would have 3 options in doing this.

1. Create a CustomerId field in the Transaction table and create relationship on the field but not enforced it and allow NULL or 0 for non customer related transactions.

2. Same setup as 1 but load a "dummy" customer and setup the constraints correctly. But I have a problem that I already have data in my customer table, so I can't take the easy route and only select the 1st customer as my "dummy" customer.

3. Create a CustomerTransaction table with TransactionId and CustomerId and create proper relationships.

What would be the best practice handeling the above scenario?

Regards,

Adriaan

Hi Adrian,

Remember that a foreign key constraint can ban be built upon a nullable column, so if you had migrated your CustomerID column from table Customer to table Transaction, and created it as nullable, then this would meet your needs (your option 1)

Do not go down the path of Option 2 as it's always ugly...

Option 3 will still leave you with a table identifying that a particular transaction did not involve a customer (a null CustomerID in the CustomerTransaction table) - this is no different then option 1 but introduces additional entities.

You've posed a design question, so I think you'll find that there is no "correct" answer and my idea of best practice will not be some elses ;)

Cheers,

Rob

|||Hi Robert,

Thanks for the reply I never realized that foreign key constraints can be built upon nullable columns, I just assumed that it would not be possible.

I always like to have a simple table structure and try not to introduce extra entities where I should not.

Thanks again!