 |
Normalizing a Database Version: Any Version
Posted on 12/08/03 06:04 PM
Posted by NoahP - UA Editor + Utterly Certified
Posts: 10404 - Loc: Lexington/Louisville KY USA
Forum: Access FAQAs
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
I posted this recently in response to another poster's request, and hope that it can help you work through the process of normalizing your table structure...
Thanks Jerry Dennison for pointing out the need of a PartsPrice table!
Some 'givens' to remember with all db's:
1) All Primary Key's (PK's) should be Autonumbers. PK's, as the inimitable Jerry Dennison (the true expert around here on normalization, IMO) puts it: "PK's are NOT for human consumption". they're meant to uniquely identify the records in a table only. The 'address' of the record in the database, if you'd like.
2) Do NOT use spaces or non-alphameric characters (#,/,?, etc.) in field or object names. Many, many, many headaches lie down that path, so steer clear.
3) Do NOT use 'Reserved' words as the name of fields or objects. There are some excellent posts in the FAQA forum here at Utter Access by Ricky Hicks and Mark (CyberCow) that identify a lot of those reserved words.
The first thing I always ask myself when designing the structure is "What end product(s) do I need?". I then step back and ask "What data do I need to get to the end product(s)?". Looking at this one another way, you could say, "What fields do I need?". I then group the related fields together into tables, evaluating each field as I do to see if I'm at 3NF. If not, I break them down, adding fields/tables until I get there. Until you get comfortable with the process, do it on paper first, before working in an actual database.
Lets look at a means to track customer orders as your end product.
Data needed would include Customer info (name, address, phone number), Inventory (Parts) info (Part #, name, price) and Order info (order date, order #, customer, parts ordered, total price) so you can reach the desired end product.
Looking at the Customer table: Naming convention (tbl in front of each table name, frm for form, rpt for report, subfrm for subform, etc.) would indicate we name it tblCustomers. Remembering Given #1 above, we'll start the table with an autonumber field to identify (id) each record, and name it CustomerID and make it the PK. The next field I decided to be needed was the name of the customer, a text field. This gives us:
CustomerID PK Autonumber
CustomerName Text
I have a series of questions I ask myself with each field as I add it, to see if I'm normalizing the table as I go:
1) Can the field be broken down further? Are there components to the field, or fields that actually make up the field I just added?
2) Will any other record ever contain this exact same information?
3) Does the field describe ONLY this record?
4) Can a entity of this type (in this case, Customer) have more than one of this field?
If I answer NO to questions 1, 2 and 4, and YES to question 3, I can figure I'm in pretty good shape to continue to the next field, in normalization terms.
Now we're at the customer address. I figure it to be a text field. This gives us:
CustomerID PK Autonumber
CustomerName Text
CustomerAddress Text
CustomerPhoneNumber Text
Asking my usual 4 questions, I decide that: 1) It CAN be broken down further. Addresses consist of other components: 1 or 2 lines of street address and/or suite # or similar data, city, state and zip code. 2) I don't believe any other customer would have the exact same street address, but could and probably will have the same city, state and/or zip code. 3) Debatable, but for this business purpose, I believe that we won't have more than one customer with the same address. 4) Yes, a customer CAN and probably will have more than one address. A lot of businesses and people have a physical address, a billing address and a mailing address with some or all of these being different. From these answers I realize that the field needs to be changed. With the fact that question 4 is yes, I can tell fairly easily (in this situation) that I need an additional table, tblAddresses. So back to the step of deciding what fields are needed. I decide that I need:
CustomerAddressID PK Autonumber
CustomerID FK (Foreign Key) to tblCustomers this links tblCustomerAddresses to tblCustomers in a many to one relationship (one customer - many addresses)
Address1 Text
Address2 Text
City Text
State Text
Zipcode Text (you won't do calculations on the zip, so it should be text)
AddressType Text (physical, mailing, etc)
Going through the same 4 question process, I feel comfortable that the first 3 fields are good to go. AddressID is the Primary Key and it IS an Autonumber. Address1 and Address2 meet all requirements as well. Now we get to city, state and zip. More than one address can have the same city, state, zip combination. Looks like we can say NO to the #2 question, a good indicator that another additional table is needed, tblCities. I remove the three bottom fields and add them to tblCities, with an Autonumber PK, CityID:
CityID PK Autonumber
City Text
State Text
Zipcode Text
Same 4 questions again: I feel good about City and Zipcode, but more than one record could have the same state, indicating a need for yet another table:
tblStates
StateID PK Autonumber
StateAbbr Text (state abbreviation)
StateName Text
No to all 4 questions means this table should be normalized. Now I have to relate it back to tblCities. I add a Foreign Key (FK) to tblCities linking tblCities to tblStates in a many-to-one relationship (Many cities can have the same state). This gives me:
tblCities
CityID PK Autonumber
City Text
StateID FK to tblStates
Zipcode Text
Now I need to relate tblCities to tblCustomerAddresses. I add a FK to tblCustomerAddresses linking it to tblCities in a many-to-one relationship (Many addresses can have the same city/state/zip). This gives me:
tblCustomerAddresses
AddressID PK Autonumber
CustomerID FK to tblCustomers
Address1 Text
Address2 Text
CityID FK to tblCities
AddressType Text
I'm now to the AddressType field. Again with my 4 questions. I decide that more than one record will have the same address type. This again means an additional table. I decide on:
tblAddressTypes
AddressTypeID PK Autonumber
AddressType Text
After questioning the fields here, I decide the table is good and go back to tblCustomerAddresses to relate the two tables. I remove the AddressType text field and add a FK, AddressTypeID:
tblCustomerAddresses
AddressID PK Autonumber
CustomerID FK to tblCustomers
Address1 Text
Address2 Text
CityID FK to tblCities
AddressTypeID FK to tblAddressTypes (many to one, many addresses can have the same type)
Looking back to tblCustomers, I no longer need customer address as a field in that table, since the relationship with tblCustomerAddresses takes care of this. This leaves:
tblCustomers
CustomerID
CustomerName
CustomerPhoneNumber
The usual 4 questions gives me the answers that I'm going to need another table. One customer can have many phone numbers. I decide on:
tblPhoneNumbers
PhoneNumberID PK Autonumber
CustomerID FK to tblCustomers (one customer, many phone numbers)
PhoneNumberType Text (mobile, office, home, etc.)
PhoneNumber Text
After analyzing this table with the 4 questions, I see that, again, I must add another table as many phone numbers can be the same type:
tblPhoneNumberTypes
PhoneNumberTypeID PK Autonumber
PhoneNumberType Text
After adjusting tblPhoneNumbers by removing the PhoneNumberType text field and adding the FK to relate to tblPhoneNumberTypes, I have:
tblPhoneNumbers
PhoneNumberID PK Autonumber
CustomerID FK to tblCustomers (one customer, many phone numbers)
PhoneNumberTypeID FK to tblPhoneNumberTypes
PhoneNumber Text
I now have 9 tables versus the original 3, just from analyzing one table! Tables now are:
tblCustomers
tblCustomerAddresses
tblAddressTypes
tblCities
tblStates
tblParts
tblOrders
tblPhoneNumbers
tblPhoneNumberTypes
Moving on to tblParts, I decide I need these fields:
PartID PK Autonumber
PartNumber Text
PartDescription Text
PartUnitPrice Currency
After analyzing with my 4 questions, this looks pretty good as is. No additional tables needed, unless later I decide to add a PartCategory, such as motors, valves, electrical, miscellaneous, etc. If I did that, I'd need a tblPartsCategory, with a FK in tblParts to relate the two tables.
Moving on to tblOrders, I decide I need:
tblOrders
OrderID PK Autonumber
CustomerID FK to tblCustomers
OrderNumber Text
OrderDate Date/Time
PartID FK to tblParts
QtyOrdered Number
PaymentMethod Text
Analyzing here, I see that I'm repeating data (fields) in a lot of records this way, since orders will, a lot of times, have more than one part ordered. This means yet another table. I decide since it line items for an order to name it tblOrderLineItems. I realize that this new table only needs to link to tblOrders, so the customer and order specific data do not go in the new table, only the parts ordered info goes here. I decide on:
tblOrderLineItems
OrderLineID PK Autonumber
OrderID FK to tblOrders (many to one, many line items to each order)
PartID FK to tblParts
QtyOrdered Number
Initial analyzing says I'm ok here. On further thought though, I realize that parts prices can and WILL change over time. I need to store that amount, to keep an accurate history. Adding that field gives me:
tblOrderLineItems
OrderLineID PK Autonumber
OrderID FK to tblOrders (many to one, many line items to each order)
PartID FK to tblParts
QtyOrdered Number
PartPrice Currency
This leaves tblOrders to look like:
tblOrders
OrderID PK Autonumber
CustomerID FK to tblCustomers
OrderNumber Text
OrderDate Date/Time
PaymentMethod Text
PaymentMethod winds up in its own table after my 4 question analyzation (more than one order can have the same payment method, I'd be duplicating data):
tblPaymentMethod
PaymentMethodID PK Autonumber
PaymentMethod Text
Removing the PaymentMethod text field and adding the FK for the relationship changes tblOrders to:
tblOrders
OrderID PK Autonumber
CustomerID FK to tblCustomers
OrderNumber Text
OrderDate Date/Time
PaymentMethodID FK to tblPaymentMethods (many to one, many orders with the same method)
The tables now are:
tblCustomers
CustomerID PK Autonumber
CustomerName
tblCustomerAddresses
AddressID PK Autonumber
CustomerID FK to tblCustomers
Address1 Text
Address2 Text
CityID FK to tblCities
AddressTypeID FK to tblAddressTypes
tblAddressTypes
AddressTypeID PK Autonumber
AddressType Text
tblCities
CityID PK Autonumber
City Text
StateID FK to tblStates
Zipcode Text
tblStates
StateID PK Autonumber
StateAbbr Text (state abbreviation)
StateName Text
tblPhoneNumbers
PhoneNumberID PK Autonumber
CustomerID FK to tblCustomers
PhoneNumberTypeID FK to tblPhoneNumberTypes
PhoneNumber Text
tblPhoneNumberTypes
PhoneNumberTypeID PK Autonumber
PhoneNumberType Text
tblParts
PartID PK Autonumber
PartNumber Text
PartDescription Text
PartUnitPrice Currency
tblOrders
OrderID PK Autonumber
CustomerID FK to tblCustomers
OrderNumber Text
OrderDate Date/Time
PaymentMethodID FK to tblPaymentMethods
tblOrderLineItems
OrderLineID PK Autonumber
OrderID FK to tblOrders (many to one, many line items to each order)
PartID FK to tblParts
QtyOrdered Number
PartPrice Currency
tblPaymentMethods
PaymentMethodID PK Autonumber
PaymentMethod Text
A final of 11 tables from an original 3!! While this may seem, at first, to be over-complicating things, it will make things so much easier as I work into forms and reports that I will end up actually SAVING time in the long run!
I, at this point, post the structure on Utter Access and ask for any suggestions. Jerry Dennison points out that I can keep a history of the price of parts and use that as a FK from tblOrderLineItems instead of a currency field to just hold the data. Liking the idea, I add:
tblPartsPrice
PartPriceID PK Autonumber
PartID FK to tblParts
DateofPrice Date/Time
PartPrice Currency
tblOrderLineItems
OrderLineID PK Autonumber
OrderID FK to tblOrders (many to one, many line items to each order)
PartID FK to tblParts
QtyOrdered Number
PartPriceID FK to tblPartsPrice
Finally, after all this work on paper (now 12 tables from the initial 3), I'm at Third Normal Form (3NF) and ready to enter the actual tables in a new blank database. Once that is done and I go to the Relationships window and define my relationships there with the appropriate Cascade Update/Delete options, I'm done (for now, until something new is added/the scope of the project changes) with the table structure and ready to look at the user interface to get the data into the db....
--------------------
For normalization links look here.
The floggings will continue until morale improves...
|
 |
● Re: Normalizing a Database Version: Any Version [Re: NoahP]
Posted on 12/09/03 11:45 AM
Posted by Jerry Dennison - Head Wizard
Posts: 14815 - Loc: South Carolina, USA
Forum: Access FAQAs
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
It isn't necessary to have both the PartID and PartPriceID FK's in tblOrderLineItems. You need only the PartPriceID. By including both, you've created a circular join.
--------------------
Jerry Dennison
Microsoft Office Access MVP 2006, 2007, 2008, 2009
Greenville, SC USA
"Advice is a dangerous gift. Even from the wise to the wise for all courses may run ill." - J.R.R. Tolkien, The Lord of the Rings
|
 |
| Page Jump |
|
Pages: 1
|
|
|
|
| Navigate |
|
|
|
| Page Jump |
|
Pages: 1
|
|
