CODEWITHSUNDEEP
database-designdatabase-schema
Stephen
Stephen

Reputation: 1617

How do I model multiple object oriented composition relationships in my database design?

I have 2 tables which could provide a relevant record representing some item approximately modeled by the following class diagram. This is highly simplified, each of the sources are distinct compound models in their own right.

UML for the OO relationship of two sources that can contain instances of items

Thinking of this in terms of a database rather than OOP the items table contains records of any possible item. If you want to know what the definition of a widget is or the definition of a gewgaw, that's the purpose of the item table. The Source* tables represent location types of physical items, with each record representing a specific location. The Source* tables also contain a large amount of disjoint information.

Collections of particular instances of widgets or gewgaws are contained in the sources, that is Source1 has 10 distinct widgets and 3 distinct gewgaws while Source2 may only contain 1 distinct widget at the moment.

To represent the relationship, if only 1 Source table existed I would normally use a pivot table with the source id and item id as foreign keys.

an ERD relating Source1 and Items through a pivot table

My first thought on how to represent the second type of store was to create another pivot table, i.e. item_source2, and then when I need to tally aggregate inventory numbers perform a union between item_source1 and item_source2.

This doesn't seem particularly elegant, violates DRY, and introduces a union where perhaps it isn't needed.

The next thought is to generalize the item_source1 table to also have a field for the Source2.id key. In practice for any given record only one of Source1.id or Source2.id would be a valid reference while the other must be null - a particular widget cannot exist in 2 locations simultaneously.

From a programmatic point of view I can create logic to test and enforce this design but it is clear to me that is not best practice but I cannot see how to solve this through database design.

I will be implementing this in a Laravel schema, but perhaps understanding the design aspect is much more critical here.

Upvotes: 1

Views: 985

Answers (1)

nvogel
nvogel

Reputation: 25526

As per philipxy's suggestion, my guess is you should implement something like this:

+-----------+    +------------+
|source1     |   |source2     |
+------------+   +------------+
+ PFK mykey  +   + PFK mykey  + <--- both reference "source"
+------------+   +------------+

+-----------+
|source     |
+-----------+
+ PK mykey  +
+-----------+

+----------------+
|shared relation |
+----------------+
+ FK myKey       + <--- references "source"
+ other attribs  +
+----------------+

Upvotes: 2

Related Questions