Database Normalisation; repeating fields

Question

I have a question about normalisation regarding what level of normal form my scenario is. I have a few tables that have the same fields - name address1, address2, postcode and phone number;

Client [id, instructor id, name, address, postcode, phone, practical, theory]
Staff [id, office id, name, job, address, postcode, phone]
Registration id, name, address, postcode, phone]
Office [id, manager id, address, postcode,    phone]

Would any normal form exist to separate them fields into something like so...

Client [id, instructor id, details_id, practical, theory]
Staff [id, office id, details_id, phone]
Registration [id, details_id]
Office [id, manager id, details_id]

Details [id, full_name, address1, address2, postcode, phone_no]

Not sure if the normalisation would work like that but it was just a thought...

Answer 1

I'll talk about 3rd normal form as it is what is most often quoted as being a reasonable level to normalize to, though there are around 10 normal forms. In essence 3rd NF means that nothing in a table, normal form really deals with relations (tables) and not the database as a whole, is dependent on anything but the key. I generally think of it as removing any candidate keys as if you have a candidate key as well as the actual key then attributes are functionally dependent on something other than the key.

In terms of the address question, having a potentially important entity such as an address contained in a table for another entity means the address becomes transitively dependent on the, say in your case, client. It means the address only survives if the client survives.

That of course is not necessarily a bad thing, it depends if you want the address if you don't have the client.

If that is the case then really the address loses it's status as an Entity in its own right and becomes a value object. Lt's say you have an Order table and you want to save the address of where it was dispatched, then having that in the Order table is fine. One key thing if you think of the address in that way is that it becomes immutable, it should never change, the address the order was sent to will always be the same, it will never change because it has happened and is a fact.

Let's say that you have a requirement to store the address for your client and also a work address, now you would end up with a situation where you would need more columns in the client table, where does it all end! That leads to practical choices for the sake of sanity and neatness, so you create an address table which could be to preserve a normal form or could just be for neatness, it depends on what you want to do with the newly created Address entity.

Moving stuff out of a table just to make the table more manageable usually results in a one to one relationship and either in the client table or out of it the normal form would most likely be the same.

It takes a little practice to move stuff around for the right reasons but it's worth getting good at it.

Answer 2

Putting columns that have the same meaning in multiple tables doesn't have to do with normalization. It has to do with a different formal principle of database design. Chris Date calls it The Principle of Orthogonal Design, or POOD.

As far as I know, the formal logic underlying POOD isn't yet as deeply researched or as widely accepted as the normal forms are. That's an observation, not a criticism.