Design question

Discussion created by tolson on Mar 14, 2017
Latest reply on Mar 14, 2017 by siplus

I'm having a tough time deciding how to handle data entry and was hoping someone could shake me from my current brain freeze.


It's an inventory control program. People are assigned items for a job.

There' s an invoice table (tbleInv) that has the job number, person assigned to the job and date of the job.

There's a main equipment table (tblEquip) that has details of the equipment, plus a link to the invoice table and a unique serial number

A Line Item table (tblLineItem) has a link to the invoice table, a unique serial number and a link to the main equipment table. This is where all the data for the specific job will be stored.


Pretty simple and things are running good.


The trouble is, there is also an accessories table. Some of the equipment (tblEquip) will have accessories but not all.  Every accessory will be linked to a particular piece of equipment, however not ALL accessories will be needed with the main equipment on every job (for instance, a camera will have to go to the job site, but perhaps only 3 of the 5 accessory lens for that camera will need to go.)


My first thought was to put the accessories in a separate table from tblLineItem and combine them in a report simply because I didn't want to have to scroll though a huge drop-down box searching for accessories. This way, when a person is entering the main equipment, they can do so and if the equipment needs accessories, they can simply click on a second drop-down box and enter all needed accessories into it's own table (tblAcc).


Now I'm not so sure and am wondering if it's better just to combine everything, main items and accessories in one table for ease of retrieval.


I have no idea why I'm going cross-eyed on this