Thread: Advice on Table structure
11-15-06, 02:00 #1Registered User
- Join Date
- Mar 2003
Unanswered: Advice on Table structure
I need some advice on the best way to set up a table please.
I am trying to put together a downtime log, we use items referred to as “Events”
An “Event” is a stoppage on the production line
An Example of an Event is “A101” this would represent “Jam up on Line”
I have set the table up as follows to look like:
EventcodeID - AutoNumber
EventCode - -Text (A101)
Description - - Text (Jam on Line)
The Letter A the Beginning Referes to a machine.
Attached to and “Event” is a “Failure Mode”
A failure mode is the reason the Event occurred in more detail
An Example of a Failure Mode would be “A101A” you notice that the Event Code is given a letter “A” at the end, the failure mode also has a description
A101A – Jam On Line – Carton Fell Over
To Break this all Down
A (Machine Type) 101 (EventCode) A (Failuremode) - – Jam On Line (Event Code Description)– Carton Fell Over( Failure Mode Description.
It is very much a Parent Child situation the Event Code being the Parent and the event code being the child.
A Parent can have many children, each time the letter at the end would be different, B,C,D,E etc..
But not all Event Codes have Failuremodes
Should this all go in one table? Or 2 or more tables and make a relationship? An example of this would link up would be really helpful
Thanks in advance
11-15-06, 08:19 #2Registered User
- Join Date
- Jul 2005
Take a look at this
The way i see it you are on the right track, just need a little more modification. I think you need 3 tables to accomplish this task, you are going to using tblmain (3rd table) to pull data from two other tables.
I hope this makes sense
11-15-06, 08:37 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
agreed you are on the right track, have a look at the relational theory section on Rudy's site
in my view you definately need more than 3 tables
I wouldn't store the fault code as a composite value, store it as 3 separate elements. It potentailly becomes a bit fiddly in the presentation layer to rebuild you composite value... no biggie write a function
Im guessing your employers would also be interested in which machine or line had the fault... so potentially thats 2 more tables (Plant Type & Plant ID)
you may need some form of resolution code (possibly another table)... is the fault cleared, who cleared it, youmay need a parts table... depends entirely on your user requirement.
You may even need to know what product was on the line at the time (eg what type of container [tetra brick/ rex/ purepak, PET bottle | different sizes], possibly even what type of product (juice, flavoured water). All such elements may be required if the evental use of the system is defect analysis... ie tryiong to fnd common patterns for fault conditions
The key is storing data atomically, ie repeating as little as possible inthe db schema, unless its a frequent fault.
an example if you were definign plant and equipment you mights want to define a manufacturer, then a sub table containing hte equipment linked to the manufacturer. This style of design can fail if you have generic parts (say electric motors, conveyor chains etc....)
keep up the good work
HTHI'd rather be riding on the Tiger 800 or the Norton