Needed a quick opinion on structure of something.
I am trying set up a section of my db to track the materials that go to a field job. Each field job can have ultiple materials, and multiple field jobs can have the same materials. I am assuming that this implies a many to many relationship. As I understand it would have to look something like this.
tblFieldJobs
Field_Job_ID (PK)
tblMaterial_Router
Router_ID (PK)
Field_Job_ID (FK) (lookup)
Material_ID (FK) (lookup)
tblMaterial
Material_ID (PK)
Material_Type_ID (FK) (lookup)
Material
Material_Cost
tblMaterial_Type
Material_Type_ID (PK)
Material_Type
My problem is this:
1) I do not know where to add the quantity of the materials, should it be on the router table f the field jobs table? I would like this quantity able to multiply by the cost of the material.
2) I do not know how to display this information, am I supposed to build a query off tblMaterial_Routerr tblmaterials and tblmaterial_type, then do a list box? or is a listbox based on tblMaterial_Router enough?