I am designing a database (simplified here) to hold parts. Each part has its own unique PartID. We can store the parts in different containers:
This is a question that has no great answer in MySQL. There are several approaches, each has pros and cons.
Option #1 - Table full of NULLs
As suggested in the comments, a
containers table that will have all columns, many of them NULL because they will not be relevant for the 2 of the 3 container types.
It's very easy to use such a table in
JOIN, but in this case the DB can't help you validate you data. You can easily insert bags that have a length or boxes that don't have one, and the DB can't enforce it.
Option #2 - Move the problem to the connecting table
In this approach, you are moving the problem from the tables that store the containers, to the tables that store the parts location.
There are 3 separate containers tables:
And one table for connecting parts to containers:
with the columns:
This option is better if you handle the inventory of containers independently and often. It makes your work with containers very easy, and places the problematic part on working with the placement of parts in containers.
You can still write a query with 3
LEFT JOINs that will get you info about the container for each part.
One more thing
If you expect to have many more types of containers, none of the above will be easy to scale. If more and more container types are added, you may want a completely different approach where all common container fields are in one main
containers table, and all the other container attributes are in a
container_attributes table with
attribute_value. One of the attributes will be the container type, others will be color, width, volume, etc.
This is a very flexible model for objects with a variety of different attrobutes that change over time.