Normalization

[olorin]

Hi people,
I think I'm about done with Normalization.
If any of you kind experts would cast an eye on the table structure (attached pic), and point out for me any pitfalls you see, I would appreciate it.
Thanks

p.s.
I know tblShipments looks a tad large but a lot of the fields are Number fields (indexed, dupes ok) set to 1 or 0. I read somewhere that the Yes/No datatype in Acess can contribute to poor performance over a network in a multi-user environment.

 

[ASherbuck]

(I am far from an expert)

But in order tables I have done I always split mine into two tables.

TblOrders and TblOrdersDetail

TblOrders would contain the OrderID, Dates, Location, CustomerID, any other data that is just going to duplicate itself if someone orders more than one item with a single order.

TblOrdersDetail contains every item with it's price, quantity, etc that is attached to the order. This way you don't end up having to make multiple orders for instances where a customer buys more than one product, you just add to the details table and link the order table to the details records.

You could probably mimic this with shipments, tblShipment, tblShipmentDetail. This way you can link more than 1 type of item to a single shipment.

http://www.access-programmers.co.uk/forums/showthread.php?t=157144
Is a thread I had not too long ago that is similiar, unfortunately it does not show any orders or shipping table but the Departments and Categories might give a good example.

 

[olorin]

Thanx for your response.
If I get your drift, you're saying that tblPOrders should be split into two tables.
table one;
tblPOrders
pkPOrderID (primarykey autonumber)
POnumber
fkCustomerID
fkProductID
fkDelAddressID

table two;
tblPOrderDetail
pkPOrderDetailID (primarykey autonumber)
DueDate
DueDelTime
BookingInRef
Comments
fkPOrderID (link to pkPOrderID)

Table one would just have the Purchase Order number and then links to the Customer, Products, and Address tables. Then linked to the Purchase Order details table in a one to many relationship. Or am I missing something?

Thanx again for your time

 

[neileg]

Nope. Unless an order only ever has a single product which in general is very unlikely, then the fkProductID needs to be in the detail table

 

[olorin]

Thanx for the reply.
So I suppose I only need the Order Number and CustomerID in the POrder Table, then everyting else in the detail table?

 

[Rabbie]

Thanx for the reply.
So I suppose I only need the Order Number and CustomerID in the POrder Table, then everyting else in the detail table?

Delivery address should be in the Order table not the Detail table although it might be even better to have it in the Customer table. This would depend on your business model

 

[olorin]

Thanx for the reply Rabbie,
how's this;

tblPOrders
pkPOrderID (primarykey autonumber)
POnumber
fkCustomerID (many side of a "one to many" link to tblOrganization)
fkDelAddressID (many side of a "one to many" link to tblAddresses)

table two;
tblPOrderDetail
pkPOrderDetailID (primarykey autonumber)
pkProductID (many side of a "one to many" link to tblProducts)
DueDate
DueDelTime
BookingInRef
Comments
fkPOrderID (many side of a "one to many" link to pkPOrderID)

 

[KenHigg]

IMHO, some of this is way over-designed and will (may) cause performance issues. The main thing I spotted, which happens all over in your model, is that you use a linked table to derive country name in the address table and I would simple use a country name look up table and store the country name in the address table. Looks like you could probably get rid of half of your relationships if you changed this everywhere it happens.

 

[olorin]

Thanx for the advice Ken,
I also thought I had too many tables in it, that's why I asked for opinions, (all of which are gratefully received).
As far as the Country Name in the Address table, I thought I had a look-up table, "tlkpCountry". Or do you mean a lookup field in the Address table?
Thanx again for the advice

 

[KenHigg]

Basically just keep the country table just don't try to maintain the relationship with the address table. The country table (less the primary key field) will be used to populate a combo box the user uses to select the country. Then the country name itself will be stored in the address table.

I need to run but will check back in after while...

fyi - Good effort trying to get things correct at this stage

 

[olorin]

Ahhhhh.
I get your drift now.
You mean a lot of the look-up tables that are linked could actually be just stand-alone tables, to refer to in combo boxes, and then store the selected data into the table that needs it. Wouldn't that amount to storing data more than once in the database?

But there again. I could use this approach, and have all the look-up tables in the FE and only send data to the BE when needed. That may help in the performance dept. (just a thought)

 

[KenHigg]

I counted 11 out of your 26 tables were like this. Think of this way. You have 10,000 records in a table. Every time you do a query the system will have to go all the way back to the related table 10,000 times just to the the correct country name. This slows the forms down, etc, etc, etc. When you have hundreds of gigs of hard drive space saving a few thousand bytes of storage isn't worth it.

 

[olorin]

Brilliant!
Thanx for the tip.
I will re-do the table relationships and post it here when finished. Then if you have time to look at it, I would appreciate any further tips.
Thanx again for the advice.

 

[olorin]

Looks better to me.
Although I do have a question.
We make more than one product for single customers, and we sell the same products to more than one customer. So I made a junction table to allow for this, "jtblProdCust".
The purchase order number we receive from customers may be for one product on a certain date, or even a lot of the one product split into many delivery dates, or for different products on different dates.
I made the PurchaseOrder and PurchaseOrderDetail tables as suggested, ("tblPOnumbaz" and "tblPOrders" respectively).
If I linked fkProdID in tblPOnumbaz to pkProdCustID in jtblProdCust, could I do away with the fkCustID field in the tblPOnumbaz?
Or should I link fkProdID to the Products table and fkCustID to the Organization table, or could that be open to errors of being able to ship the wrong product to the wrong customer?
As always, your time is appreciated.

 

[gemma-the-husky]

i haven't looked at your tables, but before you decide to get rid of your country lookup, you need to think about what you are tring to do with your dbs. There are other design issues as well that you may not have considered, and the following might help

Addresses

for most systems, they are probably dealing in one country only (their locality if you like). in the US I am sure they would have a state table to translate a state code, to a full name. This is not so common in the UK, as counties are generally entered as text. But as systems get bigger, even in the UK, I would think they go away from using text addresses and start tending towards postcode/zipcodes services pinpointing the "real" address identifier.

The point is, is that you may be shipping world wide (why else would you bother about the country), in which case a country code lookup IS probably going to be a good idea - I'll go further, you may even need to group your countires into world market zones to achieve the result you want.

Its hard to be prescriptive - you have to decide what you need in your system. The basis of normalisation is related to this, but it is important to understand in conceptual terms where normalization is taking you

Basically you want to store every data item once only in the appropriate place. The forms described as "normal forms" are really just confirmation that you have achieved this goal


so if you have an order from a customer you will need (in general)

customer table

order table

order details table
(assuming an order may comprise multiple items)
---------

now consider the delivery date, or invoice number.

If an order is ALWAYS delivered complete and intact, then the supply date/invoice number can be included on the order header.

If an order MAY be despatched piecemeal, you will need to consider how you intend to manage multiple delivery documents, to store the delivery date, and invoice date for each part of the delivery.

on a slightly different tack

If an order is a bulk order that gets "called-off" in stages (and I think you said this was the case), you may have a further issue in that the selling prices may change over time, so you need to consider how you will deal with that also.


so the data analysis phase will be pretty complex for any non-trivial system. You need to be sure you fully understand all the business processes involved, and that your data model can cope with them efficiently

Hope this helps - its just your last question indicates you have got some complex issues to consider

 

[olorin]

Thank you so much for such a detailed response.

Maybe I should give you some background as to why I developed the Dbase in the first place.

I was given the position of looking after the shipping in the company where I worked. They had no database to track any shiping details at all! Only a Sage Accounting System for Invoicing and tracking all things financial. Naturally I was on the backfoot from the start. So I developed the database using MS Access 2003, (now 2007), to help me generate shipping notes and answer questions that were asked of me by my superiors.
It worked (and still does to a certain degree) fine for generating the necessary reports and keeping check on what shipped to whom, when, and where. It even emails current bookings that have not been collected to specific Hauliers, and prints out shipping notes with all the relevant details on them, including billing/delivery addresses, products shipped, PO numbers against each product, etc,etc.

The thing is, people noticed how simple it was to generate reports, that I have now had to distribute it to 11 other workstations on the network, (BE on a file server, FE on each workstation).
Naturally, now that I am not using it as a stand alone Dbase on my desktop, there are performance issues. So I thought I would revamp the links etc. and ask for advice on better Normalization. (everything I know about MS Access I have learnt on here, and a couple of books)

As far as Invoicing and Pricing details, I have not included a provision for that. It is not required, as they want to link the shipping notes to the Sage system. i.e. whenever a shipping note is generated it sends the details to the Sage 100 Accounts system and they do whatever they want to do from there. This is beyond my capabilities, though they have sourced a firm that would set that up for them.

As far as shipping worldwide, we do. Which is why I have a link to a Country table.

So it's main purpose is to generate Shipping notes with the relevant Purchase Order number against items shipped. New PO's are entered as and when they are received, (a lady in another office does that). Certain other people need to generate shipping and stock reports. I filter-out what people can and cannot do in the Dbase by means of getting the username from the Windows logon. I have a myriad of queries to evaluate current and past stock levels etc.

Thanx again for your time.