Database Normalization and Table Structures

[Scott Price]

This reply was given when a member asked why normalisation is important.

My 2 cents worth :-)

Database normalization is a rather arcane topic that you will not be able to really understand until you work your way a little further into the database design process.

Simply put the rules of db design were developed years ago by some mathematicians working in things like 3rd order predicate logic, etc. Access and other db programs are built with these rules in mind. Yes, you can fudge your way around the way these rules/programs are built, but you do so at considerable cost to your brainpower, frustration levels, etc... Some things also not only become much more difficult to do, they can become downright impossible! A much simpler (and far more productive and safe) method is to simply accept that the rules are there for a reason.

As you work on db's more, you will begin to learn WHY the rules are there.

One simple example using your scenario: Suppose you store everything in one big table. Then next year, Quebec decides it doesn't want to be part of Canada anymore (yeah, sure... never happen...). How are you going to change the values in your 'one big table' that refer to Quebec? You will have to write code to search the fields to find each instance of the word Quebec and change it to whatever it gets changed to.

If, instead, you have a normalized db, the change is quite simple: you go to the table named something like tblProvinces, and make one change to the entry Quebec which then affects every entry that it is related to through table relationships. This concept is called data integrity, and is far more important than this simple example illustrates...

Change the example to involve accounting systems and $'s and you will understand perhaps more of the potential errors involved.

For example if you are storing employee salaries in one big table that has all information in it. How are you going to find salaried employees of a certain level of salary? Write code to search the fields to find the instances between a certain $ figure... How are you then going to change each employees salary when they get a raise? How are you going to generate a report to make sure that someone isn't (horrors) making more money than their experience/skill level?

It's only possible to dumb this subject down to a certain point! If you weren't willing to learn how to do it right, you wouldn't be here at this site asking for pointers, so I hope the above information is some help!

Scott.

[arunmen]

Thanks dude It was an wonderful tutorial.

[MMcCarthy]

Thanks dude It was an wonderful tutorial.

You're welcome. Glad it helped.

[ammoos]

Really great article. Congratulations and also thanks a lot for providing us this detailed information

[MMcCarthy]

Really great article. Congratulations and also thanks a lot for providing us this detailed information

You're welcome, thank you.

[Despina]

thank you! it really helps

[MMcCarthy]

thank you! it really helps

Glad to hear it.

[prk]

thanks a lot..

Its really helpful.

[MMcCarthy]

thanks a lot..

Its really helpful.

I'm happy you found it useful.

[sierra7]

Thanks for your excellent tutorial but I would like to ask whether there is a difference in having a compound key (OrderID + LineNo in the OrderDetails table) compared to single field unique key (OrderDetailsID ).
Does the DBMS function more efficiently?
Is this a pre-requisite for up-sizing to SQL Server?

I have a stock control system where OrderID is over 125,000 and it is not uncommon to have 10 or more Line items on an order, so my field equivalent to OrderDetailID is very large, and does not do anything for me or the users. All access to the Order Details is either by OrderID, ProductID, Due Date etc.

I have a subordinate table which tracks Batch Number, Quantity Picked so I can calculate profit, print certificates etc., but this is linked (joined) via OrderID + LineNo which is more meaningful (I used to work with dBase and suffered file corruption so often had to recover data. I have only had one instance of this with Access in the last 8 years, but I still like to 'read' the tables!)

I am considering removing OrderDetailsID and SOBatchDetailsI D as a step to improve performance (less data should mean less band-width over the network, 'cos Access is sending all data as it is not true Client/Server) Is this a move in the wrong direction because in the longer term I guess will have to upsize to SQL ?

[MMcCarthy]

If orders are produced in a batch you can add a foreign key to the order table to allow the batch number to be recorded. However, OrderID should remain a unique single primary key to the table if at all possible.

There are implications in sorting, searching and joining tables when you have a compound key and it should really only ever be used in a join table.

OrderID should be a foreign key in the OrderDetail table and as such you shouldn't need to reference the OrderDetailID at all.

If this doesn't make sense then please let me know.

[sierra7]

Sorry if I confused the issue by mentioning 'Batch'. That's another table which holds details of goods received (the stock) from which Sales Order Details (SOD) are picked. As the quantity required on the SOD may require picking from more than one Batch I therefore need a subordinate table (Sales Order Batch Details) to trace which materials have been supplied, and for other functions.

I did not want to bog you down in the details of my application, my question boils down to "If a row MUST be unique by a combination of certain fields (so indexes must exist for them) what additional function does OrderDetailID serve?"

Perhaps I complicated the issue by partly answering my question because I could (probably should!) use OrderDetailID as a foreign key on my SOBD table instead of OrderID + LineNo. However, I prefer to see my data joined by meaningful fields not just numeric 'pointers' and don't know how bad a crime this is! Obviously OrderID is a long integer and not a 15 character alpha-numeric.

Perhaps you might like to write on when 'de-normalization' might be appropriate?

I thought I may have read somewhere that it is necessary to add unique fields befor upsizing to SQL Server.

[MMcCarthy]

I realise to you numeric pointers may not seem significant. However, they are very significant to the engine behind your database that processes your queries, searches and sorts.

You say you read somewhere that you have to add them before upgrading to SQL. The truth is it's just as important to have them in Access. It is simply that Access doesn't enforce them.

[patjones]

I realise to you numeric pointers may not seem significant. However, they are very significant to the engine behind your database that processes your queries, searches and sorts.

You say you read somewhere that you have to add them before upgrading to SQL. The truth is it's just as important to have them in Access. It is simply that Access doesn't enforce them.

Your article just made me understand for the first time what a join-table is. I understood before what a many-to-many relationship is, but only in the abstract (so far in my projects I have only had one-to-many relationships). But introducing the join-table concept really crystallizes everything for me.

Thanks!

Pat

[MMcCarthy]

Your article just made me understand for the first time what a join-table is. I understood before what a many-to-many relationship is, but only in the abstract (so far in my projects I have only had one-to-many relationships). But introducing the join-table concept really crystallizes everything for me.

Thanks!

Pat

You are welcome Pat.