Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013

    Unanswered: Tables Relationship Strategy

    My db tracks equipment maintenance & repair for estate maintenance. And thanks to contributors on this site, it has proved to be very useful. I have 2 tables now.

    Table: ShopWork. Fields: JobID [Pkey]; Ynumber [text, unique number assigned to each machine]; WorkDone; WorkDate; Notes

    Table: Equipment. Fields: Pkey; Ynumber; Description; Make; Model; SerialNumber; NewDate; Active [Yes/No]; EquipNotes

    I want to begin tracking parts purchases and usage.
    Table: Parts (planned, currently a spreadsheet). Fields: Date; Vendor; Mfr.; Part No.; Part Name; Qty; Used On; Cost; Total

    My question is the relationship between Equipment & Parts tables

    I would like to know what the part is for when it is ordered.

    I was thinking of creating a category field in the Equipment table.
    Possible category names:
    Riding Mower (we have 4 different types)
    Walk-Behind Mower
    Leaf Blower (2 different types)
    String Trimmer (3 different types)
    Chain Saw (3 different types)
    Back Pack Blower (2 different types)

    For example, a fuel filter is used on multiple machines: trimmers, blowers, chainsaws.
    I order 12 at a time, and they are used as needed.

    I am curious to know how others have approached this problem.

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Given the many-to-many structure of this relationship, this is a perfect case for an intersection table. This will consist of two fields - the PK from Equipment and the PK from Parts. These two fields themselves for the PK for this table.

    You can then drop the field [Used On] from your Parts table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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