Table design (linked vs unlinked) (1 Viewer)

[nhorton79]

I am in the process of designing a database with a form for entering jobs like the attached image (Page1).

I am wondering what the best way to create the tab and subform for Items (which will be linked to my Job table (tblJob).

I have tblItem which will hold info for each item as it is added - field examples being: ItemName, ItemDescription, ItemActive etc

I would like to add Labour (Page1) and Materials (Page2) to each Item, which come from a tblInputs.

These can be added individually using Add Material or Add Labour drop-down boxes. Or, in the case of Labour I would like a certain few blank records to load automatically. I have added a checkbox to tblInput called InputAutoLoad, which when this is checked the input will show automatically – this will only happen with Labour inputs (where InputType=”Labour”, or the foreign key from the tblInputType.InputTypeID).

What I really don’t want to happen is that when these actually link to the actual record for the input. I would like them to be ‘added’

Do I need to put in a tblItemInputs which holds the added Inputs and associates them with the ItemID? Thus the tblInputs is really just a template of an input?
A many-to-many relationship?

I also would like all the null quantities of Labour to not actually store against the item causing unnecessary bloat, if at all possible.

I hope that I have been relatively clear with what I am trying to achieve.
I have uploaded a schema of where my database sits at present, rather basic at present but intending to add a lot more to it as I go. I just need to get my head around the concept of how to add these Inputs to an Item.

 

[nhorton79]

Okay. I have figured it out myself. What I needed here was a many to many relationship. :banghead:

I have created a new table called tblItemInput and included foreign key fields from tblItem and tblInput. Then I included an ItemInputTotal and ItemInputQty field.

This has allowed me to connect the two without altering the original record in tblInput. Phew.

 

[pbaldy]

Thanks for posting your solution and welcome to the site!