Normalisation Philosophies

[thefear]

Hi, firstly, thanks to all you folk on tek-tips. I've learnt more useful techniques from these forums than from any book or course!

Sycophancy aside I have a question about table normalisation the answer to which I've never been quite sure of.

I have a table that stores case details - reference numbers, dates, invoice amounts, etc. Occasionally, a special case comes into the office that has all the properties of a standard case but a few more too - more dates and a couple more reference numbers.

My question is, "Should I extract the extra fields to a new table, considering how rarely these cases crop up, or should I include the fields in the main table where for the vast majority of cases the fields will remain empty"?

This question in different guises has puzzled me many times over the years, but now it's for work it's preying on my mind.

You may be conflicting ideas on this, but all will be appreciated.

Thanks in advance

M Finch

 

[lespaul]

I might add a single field to your existing table called SpecialCase (T/F) then have a separate table which has the extra fields and the PK from your existing table. Then you can check to see if SpecialCase = True and if so run a secondary query to get the additional information.

HTH

leslie

 

[Golom]

I usually attack this sort of problem by asking "Do the additional fields provide information about the primary keys of the existing table or are they a new fact (or facts) about something else?"

If they are just more information about the primary key then they can legitimately be added as fields to the existing table. If they constitute a new entity (i.e. they have existence in their own right; possibly independent of the existing table) then they should have their own table with a relationship to the existing one.

The other question is "Does this information replicate existing data?" For example, is there already a reference number in the table and this is another reference number? If that's the case then you are looking at a possible repeating property and, while technically not a first normal form violation, such fields can complicate your life (and SQL) in unpleasent ways.

 

[thefear]

thank-you both for responding, lespaul and Golom. I'm going to take the additional fields and make them into a new table. It's the way I thought I'd take go but I just wasn't sure.

As a further question, is there a dramatic performance difference between the two methods?

 

[Olaf4]

I agree completely with Golom and lespaul.
I would do this:
a.Create a new T/F field in the main table: SpecialCase.
b.Create the other fields you need in the main table.
c.In the form, make all these fields invisible, but the T/F field. That way you do not see them all the time. Then when you need them, check the T/F field and make them visible.