I am newbie and I need to redesign this flat file database. The flat file database consist of key, (Pkey) TrackingNo, SiteID, Notes, ReceivedDate, CheckoutDate, Location, EnteredBy,Company,Carrier,Checkoutby,dbType. As you can see this is a shipping database which captures when an item has been received and when it was checkedout (deliverd).
I have an idea on some normalization but I was confused. I know that Company and SIteID is a different table because these are both unique. But what about the rest? I know that the rest are repetitive but I cannot pinpoint on how to separate these into different table.
I attached an image with data so that it is easier to explain what I have.
The rule of thumb is if field data repeats you could need to create a new table with records describing the data in that field, or more importantly data that could be linked to it. It helps to ask the question "does the field describe a data entity or real world object?" i.e. how much information could possibly be related to it, regardless of what you do with it now.
For example [EnteredBy] will refer to an employee or possibly a customer (if you allow them to place their own orders). These could both have contact information like postal addresses, telephone numbers, e-mail addresses and so on. You wouldn't want to search an orders table to get this kind of information, so the value in [EnteredBy] would be replaced by the ID field value of a record in a new parent table containing this information.
It's also worth mentioning that these records allow you to create a pick list which is easier to maintain in a table. Also, the ID field should:
1 Contain less data making indexing (ordering records et al) quicker, and your orders table smaller improving performance.
2 Not be the literal description like a name - what happens if this is incorrectly entered/changed at a later date?
You can use a query later to bring the name/description fields and the order together on screen or in a record source for a form etc.
You can apply the same idea to [Checkoutby], [Carrier], and less obviously to category fields like [dbType]. What other data might be related to [dbType]? Well, think of boolean fields whose values might be rules for data entry in other order table fields...
Broadening your view by asking "what if?" and looking for possible entities will help you break out data into parent tables. Even if you don't need to create all the fields you could possibly think of right now, creating sensible parent tables will allow you to change things more easily in the future...and you will have to change things. Users have a nasty habit of asking for more!
Finally, don't forget to create relations between parent and child tables with at least cascading updates. You need to maintain links between your tables and your data integrity. I can't think of a specific link that'll help you with the whys and wherefores but start with MS Help, and then google. Reading around will help you identify the issues.