CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designforeign-keys
user1904766
user1904766

Reputation: 49

Linking Data To Multiple Entities in Database

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:

It's important that we have specific different information for each container:

My question: What is the best way to link parts to these multiple container entities?

In my mind the simplest way conceptually is to create ContainerType and ContainerID columns in the Part table. I'm hesitant to do that, because you essentially break referential integrity having a column with an unenforced foreign key to multiple tables, because it's illegal.

We could make a big container table with all possible properties of a container, but that seems very cumbersome too.

Any help or guidance would be greatly appreciated!!!

Upvotes: 1

Views: 145

Answers (2)

Jon Wilson
Jon Wilson

Reputation: 86

The rule of thumb in a one to many relationship between tables is: the primary key of the single side of the relationship acts as a foreign key in the many side of the relationship in order to preserve referential integrity.

Create a table called containers with primary key column container.id and other columns such as container.name, container.createdOn, container.lastOpenedOn. The primary key container.id from table container is added to your parts table as a foreign key column called parts.container_id.

Upvotes: 0

Galz
Galz

Reputation: 6832

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 SELECT and 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:

  • bags
  • boxes
  • pallets

And one table for connecting parts to containers:

  • parts_to_containers

with the columns:

  • part_id
  • container_type
  • container_id

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 container_id, attribute_name and 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.

Upvotes: 1

Related Questions