|My gut feel says that you need to have a table for the parts themselves - I doubt there is only one machine that uses Pins or Clips or that size bolt.
- PartID - a unique identifier (int or guid)
- Other information as requiredParts go into Bins and I'm assuming here that each bin can only contain one type of Part so
- Bin_ID - unique identifier
- Drawer_ID - Foreign key to Drawer table
- Part_ID - Foreign key to the Part table
- Information about the bin e.g. number of parts in this binBins go into Drawers but note that the Drawer table doesn't reference the Bins, the Bins reference the Drawer, it's "owner" if you like. The same applies to the Drawer referencing it's Container
- Container_ID - Foreign key to Container table
- Other info about the Drawer e.g. Machine reference?
- Other info about the container e.g. locationIt's very easy to over-engineer database design. For example I'm tempted to introduce a Machine table but that's probably over-doing it.
You might find these links helpful too:
Database Design Tutorial for Beginners - William Vincent[^]
How to Design a SQL Database[^]