Normalisation and relationships - first attempt!

[CharlieC]

Hi everyone

Have been reading for days and have finally braved an attempt at some tables. I would be very grateful if anyone could give me their thoughts on them as I have learnt the hard way that if this bit is wrong, there is no point carrying on!

Please let me know if I've not provided enough information.

Thanks in advance
Charlie

 

[georgedwilkinson]

You have a table named "Results". What is the objective of that?

 

[The_Doc_Man]

First things first.

Describe your problem in words. Showing a diagram is meaningless to us when taken in isolation. The reason is that function guides design. We are looking at the tail-end of the design process, not the beginning - the function. We need to know something about the goals. Because I can imagine a couple of scenarios where what you have is perfect - and at least a couple where it bears no relation to reality.

Since this is a highly verbal site, please consider using words to describe the problem (though pictures DO help.)

 

[CharlieC]

Really sorry, had written a big explanation of what I was trying to do but ditched it because I was worried it sounded daft (how ironic!!)

I am supposed to be creating a way to store lab results, the ultimate aim being that you enter the results and find out whether the product is within some set limits and has passed or failed.

There are about 6 different products, manufactured in four different countries and tested in four different labs. There needs to be a way to search by the order info: customer sales order numbers, BL number, contract number and PO number. The results table has got all the test details and results in it.

Hope that has helped explain it a bit?

Thanks again
Charlie

 

[georgedwilkinson]

Actually, along with your picture that helps a lot.

You might want to adapt a standard in naming. Your current standard is very close to what I use but is inconsistent. You might also want to remove ambiguity in the names so that people (like us) who look at your system will understand what you're trying to do.

You have Products and Results, both of which are plural and Order and Laboratory, both of which are singular. I always use all plural table names (it's easier to talk about them in real English and is the standard adopted by professional data modelers). Column names should be singular. So your table names would become: Laboratories, Products, Results, and Orders.

Further, you'll want to remove some of the ambiguity in the names. I would suggest changing Results to TestResults and Order to something that describes it a little better. (is it a purchase order, a sales order?)

Now, how to you arrive at results? Is there only 1 result per product being tested or do you sample a portion of an order to test? If it is one testing event per result arrived at, your model MIGHT be close to correct. However, having modeled this exact scenario before, you might want to create a Tests table. And if you're worried about ISO 9001, you'll want a TestMachines table. And if there is more than one product on an order, you'll want an OrderLineItems table.

Do the products have serial numbers? Are they a part of a batch of something larger that once had serial numbers? Are you going to want to track that?

The model SEEMS a bit smallish and some of the tables are a bit "wide" for what I think you're doing but if it works, way to go! I guess you're asking for our opinions though, so you don't make a mistake you can't live with later, so I'll be happy to answer any followup questions you may have on this. Think into the future...what's gonna happen if somebody says we have to track another different kind of test result? This design won't cause great happiness if that happens. Thus TestResults could use some revision/normalization.

Whether this model (with the revisions I've suggested) is complete and accurate or not really depends on whether there is another system tracking some of the other important data points in question.

 

[CharlieC]

Thanks so much for your detailed reply (and sorry for the delay in thanking you, have been on holiday.) I have now revised the layout and separated out the Tests table from the Testresults table.

You were right in pointing out that multiple batches per Salesorder would mean that it won't work - I completely missed this. It is actually potentially more complicated in that one Salesorder could be made up of several batches, each of these would then be tested every 250 Tonnes so multiple tests per batch as well. Haven't got a clue how to link this into the other tables though? Would I just create a Batches table and link this to Salesorders and Testresults?

Thanks again for your help.
Charlie

 

[georgedwilkinson]

Sorry, I'm trying to keep up but I am very unfamiliar with the terminology you're using.

You use as many tables as you need to accurately define the data. See my recommendations above for more pointers. You link tables together via foreign keys which can be defined on the relationships menu item in Access.

Does that help?

 

[The_Doc_Man]

When defining a database, you must identify what you are tracking. Normally, when I say that, folks look at me askance. Except that you won't, because Geroge pointed out the bit about multiple batches per sales order. You already understand that you had failed to completely identify something that was being tracked.

Here is the way I do this: Before I write a line of code or design one field of the database, I take a design or requirements document to the nearest large white-board. Along with a case of sticky-note pads. For each requirement, I look at what will be needed to meet that goal. Where I decide a table is needed, I note the table on the board and populate it with a couple of example records via sticky notes.

When I have completely stepped through each requirement and made good notes, I have the beginnings of the design.

Here's an example:

I note that I will have sales orders. In order to track a sales order, I need a sales order number (and a place to store it), a customer to whom the sale was made (and a place to store customer data), and the details of the order such as a line-item table (and a place to store line-item data). The sales order and line-item are different but related, so I look for parent/child relationships. (Which in that case, I would find.) The details relate to a product or service I sell so I'll need a Sellables table to store that information. Sellables somehow relate to costs, but I need to know the problem intimately to tie that all together. For instance, do costs come strictly from components? There's a table for you. Do costs come strictly from labor? Different table. Do both time and materials contribute? That's TWO tables implied from the simple statement of having sales.

Now, if you take specific requirements into account to reflect your business rules, you find more things you need to either store or link together. Draw lines from your tables to show how they relate (which of course leads to action on the relationships window.)

This is the phase to be methodical. If you ain't methodical here, you are sunk. Not that there is ever a time to get really sloppy, but sometimes things get more sensitive than other times. Up front is the most critical because an error up front cascades its effect to the tail end of the process. Retrofitting an early mistake always costs more the longer you wait to fix it.