Thread: Tables Relationship Strategy
07-02-13, 16:38 #1Registered User
- 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)
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.
07-03-13, 09:01 #2Registered User
Provided Answers: 2
- Join Date
- Sep 2006
- Surrey, UK
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.