Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    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.

    A=Filler
    B=Packer
    C=Box Maker
    etc

    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

    Example:

    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

    Andy
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2005
    Posts
    144

    Wink Take a look at this

    Andy,
    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
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    Machine Code
    Error Code
    Failure Mode

    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
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •