Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    help with design/relationships

    hi all, I'm trying to design a database that keeps track of some industrial machinery and the inspections that are made on them. I think I have the tables right, but a couple of the relationships I'm not so sure about... The tables are

    tblClient
    tblMachine - each Machine belongs to only one client, and has many components
    tblComponent - related to one Machine
    tblInspection - the tricky part....this is the monthly inspection on each Machine

    I'm not sure how to relate tblInspection to the others...an inspection is (currently) something that is done to a Machine (and all components thereof), giving a 1-M [Machine-Insp.] and a M-M [Comp.-Insp.], but it seems more logical to have the inspection as a 1-M with the component instead.....which then makes it a 1-1 between Inspection and Machine. Should I even have all three tables linking each other, or just two links?
    The main goal of this dbase is to produce a report that shows, for each Machine, a list of its components (columns) and data for each components inspections (rows). Also, each component can be replaced, but the inspection history shouldn't worry about that (ie. the history should go back to the beginning of the machine, not the beginning of that specific component), so maybe I need another table to differentiate between a machine's component, and the specific item that is currently installed as that component on that machine?
    I have some issues with how I'm gonna do this report, but I guess now I should concentrate on getting the design right....so any suggestions? All help is much appreciated thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It isn't correct to say that making Inspection 1-M with Component "makes it a 1-1 between Inspection and Machine". It could be like this:

    Machine --< Component >-- Inspection

    i.e. Inspection has a M-M relationship with Machine.

    In fact I think there is a missing entity here: you have the Inspection, of which there is 1 per Machine per month, but you also need the "Inspection-Component" entity that has the results of the Inspection for a particular Component. So a crude diagram would be:
    Code:
    Machine ------------------< Inspection
      |                             |
      |                             ---<
       --< Component ------------------< Inspection-Component

  3. #3
    Join Date
    Oct 2004
    Posts
    75
    thanks alot andrewst, I think that's exactly what I'm after, will try it out tonight. Thanks again!

Posting Permissions

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