0

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...

manlio
  • 18,345
  • 14
  • 76
  • 126
Callum
  • 29
  • 3
  • An address table? yeah, sure. I'd keep name in the entity, however. SOmeone who has faced a similar problem: http://stackoverflow.com/questions/5530681/normalize-an-address – mcalex Jan 10 '13 at 12:59
  • Thanks, but i was wondering what type of normalisation this would come under. NF3? i dont understand it 100%.. – Callum Jan 10 '13 at 13:07
  • 1
    Isolating similar fields from multiple tables is not a matter of normal forms. Each of your original tables are all 3NF already. Collecting up all similar data from multiple tables into one isn't a normalization technique. The reason to do something like this is more likely to be rationalizing your maintenance code for this information. The only possible exception might be if your tables are all subtypes of some supertype which shares address properties. If that is the case then you've inverted the relationship a little bit. `Details` would actually be `Supertype`. – Joel Brown Jan 10 '13 at 13:25
  • Oh. Theory. From my understanding, you're in 3NF and you're dealing with functional dependencies which AFAIK aren't strictly part of normalisation. Putting separate addresses in an address table helps with keeping them correct/validated and is useful if clients are also staff, for instance. Some useful info about 1/3 of the way into this doc: https://courses.washington.edu/info200/win12/Readings/dbNormalization.htm – mcalex Jan 10 '13 at 13:31
  • Thankyou, thats given me plenty of information – Callum Jan 10 '13 at 14:16

2 Answers2

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
-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.

M H
  • 112
  • 8