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!

Relate a Contact to Customer

I have a contact table and a customer table. The two tables will contain columns like

First name

Last Name,

Date of Birth

Post Code,

House Number

Street Name

etc.

I would like to find the different combinations in which I can relate the customer and contact data.

Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me.

From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?

I am very new to Data Mining and would like to have some direction as to how to progress with this.

The fuzzy match functionality in Integration Services is a better solution for this problem.

Data Mining requires a training set that already has the patterns you wish to discover - once you train a mining model with that data set, you can then apply those patterns to new data and predict missing information (potentially, depending on the type of algorithm you use to build the model).