Normalization Check

[Nicolette]

Ok so I decided to start over because I really messed up . Thats ok I'm learning lots! So I have spent much time trying to make sure my tables are normalized as much as I possibly can have them. I did a ton of reading and research and I kept reading that Access Databases work best when normalized into the smallest possible parts and linked together with relationship and queries so I worked off that theory. I'm expecting to make changes so I have only done the tables I haven't touched anyother object. so here are my tables, Let me know your thoughts and ideas. Thanks So Much!

tblCustomers
(PK) - CustomerID
FirstName
LastName
Company


tblAddresses
(PK) - AddressID
AddressType
CustomerID
RecipiantName
AddressStreet1
AddressStreet2
AddressCity
AddressState
AddressZip
AddressCountry


tblContact
(PK) - ContactID
CustomerID
PhoneNum1
PhoneNum2
PhoneNum3
EmailAddress
WebsiteAddress

tblState
(PK) - StateID
StateShort (abbreviation)
StateLong (writtenout)
TaxRate

tblCountry
(PK) - CountryID
CountryName

tblEmployees
(PK) - EmployeeID
FirstName
LastName
Title
BirthDte
HireDte
StreetAddress1
StreetAddress2
City
State
ZipCode
Country
HomePhone
Extension
Supervisor
Notes

tblSupplier
(PK) - SupplierID
CompanyName
StreetAddress1
StreetAddress2
City
State
ZipCode
Country
PhoneNum
EmailAddress
WebsiteAddress

tblManufacturer
(PK) - ManufacturerID
CompanyName
StreetAddress1
StreetAddress2
City
State
ZipCode
Country
PhoneNum
EmailAddress
WebsiteAddress

tblInventory
(PK) - SKU
ProductTitle
Description
ProductTypeID
Size
Color
Material
ProductCost
ManufacturerID
SupplierID
ReorderLevel
StockLocation
DteAdded
Discontinued
Picture
Notes

tblOrderHeader
(PK) - OrderID
CustomerID
OrderDte
OrderStatus
TaxRate
ShippingMethod
ShippingRate

tblOrderDetails
(PK) - OrderDetailsPK
OrderID
SKU
OrderQty
OrderPrice
ProductTypeID

tblPaymentMethod
(PK) - PaymentMethodID
PaymentMethodName

tblPayments
(PK) - PaymentID
OrderID
PaymentAmt
PaymentDte
PaymentMethodID

tblShippingMethodRate
(PK) - MethodRateID
MethodRateName
ProductTypeID
ShippingRateBase
ShippingRateAdditional

tblProductType
(PK) - ProductTypeID
ProductTypeName

tblTransactionType
(PK) - TransactionTypeID
TransactionTypeName

tblShipments
(PK) - ShipmentID
OrderID
TrackingNumber
ShipmentDte
ShipmentCost

 

[PNGBill]

Wow, you really have covered all the bases.

Check and remove any duplicated fields.
eg tblOrderDetails has SKU - important. But it also has ProductTypeID which is in the same table as SKU so can be obtained as required.
It is more important that a database has little or no redundant data which repeating ProductTypeID is.

Each field should have a purpose and be some data that can not be calculated / concatenated from existing data.
The only time a field from one table should be listed in another is to provide a link between the two tables that can not be obtained some more direct way and once this is done then no other field should be repeated in the two tables.
eg CustID is directly linked to OrderHeader so should be included and OrderDetail and orderHeader are directly linked.

 

[vbaInet]

Just wanted to point out two things:

1. I would be inclined to have a Shipping Address and Billing Address field related per order, probably in tblOrderHeader. Reason being, if you change a shipping address, it would reflect on old dispatched orders. Unless you're not worried about that but in that case you wouldn't be able to tell whether an order was sent to that new address or not.

2. If you're going to have a RecipientName field in the Addresses table then ensure that it only has a value for Shipping addresses only. Not absolutely crucial though.

 

[Nicolette]

eg tblOrderDetails has SKU - important. But it also has ProductTypeID which is in the same table as SKU so can be obtained as required.

ProductTypeID is for the shipping so I can assign the shipping rates depending on the type of product it is.

So do you see anything else? I'm looking for any and all input... do you see anything else that could be split into another table?

 

[Nicolette]

I would be inclined to have a Shipping Address and Billing Address field related per order, probably in tblOrderHeader. Reason being, if you change a shipping address, it would reflect on old dispatched orders. Unless you're not worried about that but in that case you wouldn't be able to tell whether an order was sent to that new address or not.

so I should update tblOrderHeader to include the field ShippingAddressID and
BillingAddressID which I would do through a query specifying which AddressType for each one correct?

If you're going to have a RecipientName field in the Addresses table then ensure that it only has a value for Shipping addresses only. Not absolutely crucial though.

I thought about that and have been doing some reading to try to determine the best method I keep thinking a validation rule if I can do it where it says AddressType is Shipping otherwise not needed.

Thoughts?

 

[vbaInet]

No, an order would have it's own address field or address fields for shipping and billing. Nothing related to the Addresses table. Think about it, if you placed an order a month ago and you used "House 111" and that order got delivered to "House 111" obviously, but 2 months later you moved to "Flat 123" and the address got changed. If you were to trace where your first order was sent to, it will show "Flat 123" because the changes were cascaded.

Validation rule or using vba. Either way works.

 

[PNGBill]

Not that easy to give definitive answers until the process of setting up your forms shows how you use the data, given that your tables "look" correct.
I wouldn't be in too much hurry to increase the number of tables.

What a "Text Book" may advise, on this issue, may not always be relevant.
eg, how many Document Options are there ?
Invoice, Order, Delivery Doc?
If the list is unlikely to increase, you would not be wrong to simply have the selection as a value list in the form's text box control where you enter the data rather then another table.
I know another table will keep the names "on Record" but if taken to the N'th degree, you could have your database littered with confusing tables.

I know on our database it is annoying to have to pull into a query another table just to decide on Mr & Mrs when the original table could have just as easy as 1, 2 or 3 had Mr, Mrs Ms.

I would include a lot more fields in tblCustomers eg Address, TelNo etc

I would not have tblSuppliers and tblManufacturer.
The two are essentially the same.
If the Manufacturer is the supplier, then they are a Supplier. If not and it is important, then have one field on tblInventory that holds ManufactuerName.
If it is still important, then have this field as the link to a tblManufacturer but why do you need all the contact details of the Manufacturer if they are not the supplier?
Another option is to have a longer product description that can include the manufacturers name, if required.
If you want to know if a Supplier is a wholesaler or manufacturer, then add a field to tblSupplier that holds Wholesaler, manufacturer as a one word.

tblPaymentMethod & tblPayments.
If the Payment Options really do require a table consider just having One Field in your table, PaymentOption.
That way, when you select the option, Credit Card, PayPal is included rather then 1, 2 or 3 which then has to be referenced back to tblPaymentMethod to get the text, or an If Then used in your query but if you do that, then why not just do this in the first place and hardcode the options.
Databases were originally designed for larger corporations & governments and by default, they would have had a Lot of data and the computers would have had very small hdd storage if indeed any. hence the preference to store "1" rather then "PayPal"
Today, you have graphics which wipe the notion of small hdd's off the table.
Your graphics field will make any saving in data size mentioned here a non issue.

Ditto, tblState, can the State Short Code be your Primary Key Field? You would then store a two or three digit text string in your tblCustomer or tblOrderHeader rather then 1 or 15 which really won't mean much and agian, will require another table in your query to translate when sometimes, the Short Code will suffice.

One final bit....
Are you sure you need to trash your work to date?
Replacing a form, inserting a field into a table, updating a query are all tasks that will never stop.

 

[Nicolette]

I would include a lot more fields in tblCustomers eg Address, TelNo etc

I ran into an issue where I have regular customers that either split ship or have a feww different addresses they have their orders shipped to so i created tblAddresses to hold all addresses for each customerID so I could easily choose which address the order is being shipped to. I could keep the billing address in tblCustomers and that would remove the need for the address types...

I would not have tblSuppliers and tblManufacturer.
The two are essentially the same.
If the Manufacturer is the supplier, then they are a Supplier. If not and it is important, then have one field on tblInventory that holds ManufactuerName.
If it is still important, then have this field as the link to a tblManufacturer but why do you need all the contact details of the Manufacturer if they are not the supplier?
Another option is to have a longer product description that can include the manufacturers name, if required.
If you want to know if a Supplier is a wholesaler or manufacturer, then add a field to tblSupplier that holds Wholesaler, manufacturer as a one word.

no it is of no huge imporance to me beyond knowing who mades it, so removing such info is the obvious result so I will simply alter tblInventory to include that as text.

tblPaymentMethod & tblPayments.
If the Payment Options really do require a table consider just having One Field in your table, PaymentOption.
That way, when you select the option, Credit Card, PayPal is included rather then 1, 2 or 3 which then has to be referenced back to tblPaymentMethod to get the text, or an If Then used in your query but if you do that, then why not just do this in the first place and hardcode the options.
Databases were originally designed for larger corporations & governments and by default, they would have had a Lot of data and the computers would have had very small hdd storage if indeed any. hence the preference to store "1" rather then "PayPal"

Again experience speaks a value list does make more sense so tblPayment methods will be removed

Today, you have graphics which wipe the notion of small hdd's off the table.
Your graphics field will make any saving in data size mentioned here a non issue.

I agree the DB will be large in size but to me the graphics are important since so many of my items have the same title and description

Ditto, tblState, can the State Short Code be your Primary Key Field? You would then store a two or three digit text string in your tblCustomer or tblOrderHeader rather then 1 or 15 which really won't mean much and agian, will require another table in your query to translate when sometimes, the Short Code will suffice.

Altering this also

One final bit....
Are you sure you need to trash your work to date?
Replacing a form, inserting a field into a table, updating a query are all tasks that will never stop.

My reason for starting over is I have 20 versions all with different updates and some parts are no longer working and i'm lost in what queries and such are actually being used, which ones should be deleted I thought starting over with a clean slate and a bit more organized work be easier

 

[PNGBill]

You can have your Billing Address in tblCustomers and also, default Mailing Address.

Then, in your OrderForm, have a check box that is ticked if the mailing address is different from the Default address or a Command Button that is clicked if address is different.
When Clicked you will open a form that displays the Mailing Address Options used by this Customer (tblMailAddAlt) and you can select which one to use for this order or add a new addres if required and when selected you are returned to the Order Form and the mailing address is as selected.
How to handle this is a Form Issue for when you are ready to do this.

A different issue is where one order has many Mailing Addresses.
This would normally be handled by Delivery Dockets.

New Order rec'd - Create OrderHeader and OrderDetail records.
You now have a record of mrs Smith ordering what items on what date and what qty of each.

You create delivery Dockets - I know, a new can of worms but very common practice.
By default, the first Delivery Docket is created with All Products and All Qty's to be sent to the Mailing Address.
Your tblDelDoc has fields DelDocPK, OrderDetailsPK, SKU & DelQty Plus 3 or 4 Mailing Address Fields and DateDel
You edit the qty - change 10 to 4 and click a button.
A new Delivery Docket is presented to you for all the remaining items.
You again edit the qty's and edit the mailing address and click a button.
The process is repeated until the sum of DelQty = Sum OrderQty.
Some code will assist here - easy to get help when needed.
Code will also not allow the DelQty Sum to Exceed OrderQty (tblOrderDetails)
You can have the left hand side of your screen showing the delivery docs raised as you proceed to go through the qty's until all are covered.

Your order is complete when All delDoc's related to tblOrderDetails are Despatched (DateDel not Null), tblOrderHeader record has been paid for.

Should not wish to go down the delDoc road then consider just creating 4 original orders for Mrs Smith on day one rather then One order with 4 different shipments.
You may then need to handle mrs Smiths payment in a reverse way to delDocs as she will make one payment for 4 Invoices.

 

[Nicolette]

A different issue is where one order has many Mailing Addresses.
This would normally be handled by Delivery Dockets.

New Order rec'd - Create OrderHeader and OrderDetail records.
You now have a record of mrs Smith ordering what items on what date and what qty of each.

You create delivery Dockets - I know, a new can of worms but very common practice.
By default, the first Delivery Docket is created with All Products and All Qty's to be sent to the Mailing Address.
Your tblDelDoc has fields DelDocPK, OrderDetailsPK, SKU & DelQty Plus 3 or 4 Mailing Address Fields and DateDel
You edit the qty - change 10 to 4 and click a button.
A new Delivery Docket is presented to you for all the remaining items.
You again edit the qty's and edit the mailing address and click a button.
The process is repeated until the sum of DelQty = Sum OrderQty.
Some code will assist here - easy to get help when needed.
Code will also not allow the DelQty Sum to Exceed OrderQty (tblOrderDetails)
You can have the left hand side of your screen showing the delivery docs raised as you proceed to go through the qty's until all are covered.

Your order is complete when All delDoc's related to tblOrderDetails are Despatched (DateDel not Null), tblOrderHeader record has been paid for.

I'm not sure what you mean by Delivery Docket... Is that another table or form. I see the underlying idea of spliting the order which is something I had thought of but had no idea how to approch.

 

[PNGBill]

If you visit a Store, say a builders supply operation and give them an order.
They will produce one Invoice that you then pay for.

They will give you a Delivery docket for the items you take with you and produce another for the items to be delivered the next day by truck.
The two dockets added together, total the goods on the Invoice.

A Delivery docket will resemble an Invoice but does not normally have prices. Just items and Qty along with of course any delivery details and special instructions.

This method will allow you to treat One order as One Invoice and Payment yet handle the 2 to infinity actual deliveries.

You will need a DeliverTable but no prices, just items.

Other options are:

Record 4 separate Orders on day one but then of course, you have to handle the One Payment. May appear easier then the Delivery Docket option but delivery Dockets are more realistic to how you handle the orders.
Your Customer would not be interested in make 4 Payments, I assume.

One Order, One Invoice and One Payment but Multiple Deliveries is my preference.

Another option is to allow Back Orders.
This method will be One Order, One Invoice and One Payment but you have a field in tblrOrderDetails for QtyOrdered and QtySupplied.

Where the qtysupplied is less then qtyordered, your system raises a new Order, same customer, for the missing items (Order marked Paid).
Repeat the process until as you go through the qty's.
Each of the Back Orders will have the same Customer but different Mailing Address.
This method just requires the one extra field in tblOrderDetails and a field in tblOrderHeader that can hold the Original OrderID. Add a Status "Back Order' to your Status List and these created orders will all be marked as such.
This will be best done with some good code to automatically create the Back Orders. Manually doing this is asking for trouble.

Trust the above makes sence and gives you some options - That I can think of.

 

[vbaInet]

My thoughts:

On the order form you can have two subforms at the buttom for billing and shipping addresses. You can add as many billing and shipping and address per order as long as the orderID is not the unique ID. So if you had Shipping and Billing address fields in the OrderHeader table your PKs would be OrderID, CustomerID, OrderDate and Postcode.

Through this you can clone an order to be used for multiple orders. VBA will certainly be required.

 

[Nicolette]

yes the Delivery Docket does make the most sense from what i'm reading, and seems to be the most efficient and the easiest.

So I need to make the table and it would resemble like the following

tblDelDoc
(PK) - DelDocPK
OrderDetailsPK
SKU
DelQty
Address split into additional field and repeted
DteDel

Wouldn't I also need the orderID? what about customerID?

Then I would need a form that would allow me to fill in this table and a way to check to make sure the quantities are correct compared to the original order.

You can have the left hand side of your screen showing the delivery docs raised as you proceed to go through the qty's until all are covered.

do you mean some sort of a split form?

 

[PNGBill]

This probably require subforms.
A single on the right hand side that you are working on and a continuous on the left hand side that shows the orders/invoices belonging to the order you are working on.
You would have the main form requeried each time something happened.

You would get the forms working as themselves before attempting this.

 

[PNGBill]

Just so you don't get too confused. There are many ways to handle this issue and they all require the basic tables you are working on.
The issue of multiple deliveries should be considered now but even if, as you proceed, a change of thought happens, it should not effect the main database and how it works. ie, shouldn't have to "bin" it again.

With the assistance of the more experienced members, you can have code that perform "Magic".

Think of the Database as compartments in that if you change the way you handle an order, it should be able to just effect one or two tables and related queries & forms and not require a rethink of the whole system.

 

[Nicolette]

I understand I just started feeling very unorganized and thought it better to start over and be more organized from the start.