Hi, I am trying to build a database to track a few things, and after trying some different things, I can't seem to get the correct design.

I have a list of instruments (about 50 in all), and each instrument can have more than one lot #. For example, I have 90 mallets, and 30 of them have lot #050194, 30 have #050195, and 30 have lot #050196. Also, I have different sets that the instruments go into. For example, set #1 contains 1 mallet lot #050194, 1 handle lot # 050197, and so on with 30 other instruments with lot #'s.

I would like to design the database so that I can pull up what instruments are in which certain set. And from there, I have customers who borrow the sets, so I would like to track which customer has which set. There are about 100 different sets, and one customer can have more than one set at a time, but 1 set can only be with 1 customer.

Any suggestions would be appreciated. Thanks!

why not assume that EVERY instrument will be a part of a set? The set may only contain one instrument, but, it would simplify the design...

So, you would have a SET table, containing fields set number and set description - data re the set entity.

You would have an INSTRUMENT table, containing data about the individual instrument entity, including the set to which it belongs, and the LOT to which it belongs.

There would be a 1:N relationship between table SET and table INSTRUMENT

You would have a CUSTOMER table, containing data specific to the customer entity

You would have a ASSIGNMENT table, which is the intersection table in a many-to-many relationship (CUSTOMER to SET)

You can always use the count aggregate function, either in a query, or in a view, to return the number of instruments per set, as/when needed.

You would have a LOT number table, holding data specific to the lot number (number, date, etc.) The INSTRUMENT.LOT_NO column would have a foreign key relationship to the LOT.LOT_NO column.