Sql Server and Foreign Keys

mewso

I have a question for anyone with experience designing databases. I've been designing them for many years but I have probably not been doing things right all the time with foreign keys.

The question can be best served with an example. I did a customer contact system many moons ago and 2 of the tables I have would be enquirys and categories. Now an enquiry can be categorised but not necessarily if the user is unsure so I input a zero if no category is selected. This means I can't create a foreign key between the two.

Now the other option is to create a category called "Uncategorised" for example but here is where my problems arise. Every category is linked to a departments table (in another database) and all cateogries will be linked to a department table so here a foreign key realtionship could properly exist but of course if I introduce an "Uncategorised" category it won't have a link to a department.

Yet again we could add a Department called "None" but now we are entering into a situation where it seems to me we are not correctly representing the data. Departments is a table used by many applications. For example in a staff situation you let users lookup all staff in a department. So we don't want the Department "None" to appear in this kind of lookup and now we find a need for a field in the departments table to indicate if it should appear in a lookup list and so on. Basically you end up going round in circles.

So how far do we go? Do we dispense with foreign keys where it isn't workable are try to make them fit in all cases?

Webmonkey

You could allow the foreign key constraint to be not null though I don't think this is good

Ginger

musician wrote: »

I have a question for anyone with experience designing databases. I've been designing them for many years but I have probably not been doing things right all the time with foreign keys.

The question can be best served with an example. I did a customer contact system many moons ago and 2 of the tables I have would be enquirys and categories. Now an enquiry can be categorised but not necessarily if the user is unsure so I input a zero if no category is selected. This means I can't create a foreign key between the two.

Now the other option is to create a category called "Uncategorised" for example but here is where my problems arise. Every category is linked to a departments table (in another database) and all cateogries will be linked to a department table so here a foreign key realtionship could properly exist but of course if I introduce an "Uncategorised" category it won't have a link to a department.

Yet again we could add a Department called "None" but now we are entering into a situation where it seems to me we are not correctly representing the data. Departments is a table used by many applications. For example in a staff situation you let users lookup all staff in a department. So we don't want the Department "None" to appear in this kind of lookup and now we find a need for a field in the departments table to indicate if it should appear in a lookup list and so on. Basically you end up going round in circles.

So how far do we go? Do we dispense with foreign keys where it isn't workable are try to make them fit in all cases?

A uncategorised category is a category still.. so you could have created keys that way, as long as you used some unique indent for each category.

You can also have it superkeyed by department meaning that maybe every department has a category called none, but in the case of a superkey its a Dept 1's Uncat, Dept 2's UnCat etc...

A bit of duplication but if you normalise it a bit and have a DeptCat table you link each department to their categories meaning that you create the category once and have it accessible to each department..