1

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.

Stephen
  • 1,607
  • 2
  • 18
  • 40
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy May 01 '19 at 21:24
  • This is a case of a common type of anti-pattern for DB/SQL subtyping/inheritance. See also re 2/many FKs to 2/many tables. PS Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS "feed a foreign key" is not clear, please use proper technical terms, a FK constraint has a list of columns in a table referencing another list of columns in a table. – philipxy May 01 '19 at 21:29
  • @philipxy I am reviewing the suggested answer. I wasn't thinking in an object oriented paradigm and thus I wouldn't have tripped over this phrasing. The relationship isn't inheritance, which is an "is a" relationship, rather this is composition relationship - "has a". The situation is closer to "the item is on the shelf" or "the item is in someone's pocket" where all I care to know is the total number of items available in any location. In any event you've gotten me off in the right direction, I think. – Stephen May 02 '19 at 01:55
  • 1
    I'll make some edits to the question to improve the quality, regardless of whether it is going to close, and I will report back on whether any significant differences between inheritance and composition appear in my final implementation. – Stephen May 02 '19 at 01:55
  • I do not believe this is the same pattern, or at least I don't see how to move forward from the provided link. – Stephen May 02 '19 at 03:03
  • You have two kinds/types of source. Two kinds/types of in-pocket things. That is subtyping. DBMS subtyping idioms are used when there are subtypes of things in the business. You seem to misunderstand. Relational subtyping idioms at the link are meant for appropriately modeling businesses. The link asker was thinking in OO terms so they used "inheritance" for "subtyping". The "composition" you think you see is an irrelevant similarity in pictures--but not their meanings--used in the RM & ER vs used in OO. Throw away your current design, it is an anti-pattern. – philipxy May 02 '19 at 03:05
  • The general idea is to have a table Super "[x] is a thing, with thing properties [p1] & ..." & tables SubA "[x] is a thing of type/kind A with A-particular properteis [pa1] & ..." & SubB "[x] is a thing of type/kind B with B-particular properteis [pb1] & ...". The various idioms & your design can all be defined in terms of each other, but some are just more complicated to manipulate, constrain and/or query. Suggest you read a textbook on information modeling & database design. Especially the fact-based methods like Object-Role Modeling. – philipxy May 02 '19 at 03:12
  • @philipxy You seem to misunderstand, I have 1 kind of thing. I have 2 places the thing could be inventoried. The "places" have completely different natures. Other than the idea that each has a collection of things they are disjoint. In OO terms the only thing they would have in common is they could both belong to an abstract interface of ableToCollectThings. Are you suggesting that I should model the abstract interface and then apply the sub class patterns as respects my "places"? – Stephen May 02 '19 at 03:29
  • 1
    In my last message I meant to say, "two kinds of located things". So tables related(x, thing), thing(thing, p1, ...), pocket_thing(thing, pt1,..) & shelf_thing(thing, st1, ...). The linked answer is about *subtyping*, not OO. Maybe I misunderstand though. PS Don't make an OO model then trivially map it to a relational DB. (Although it's what ORMs do.) That's not a relational design of your business, it's a relational design of your OO design of your business. (Relational design is identifying necessary & sufficient predicates/tables to record statements describing your situations/state.) – philipxy May 02 '19 at 03:48
  • 1
    I would like to upvote this but I can't follow much of the text description in detail other than your "To illustrate [...]". Although I can reverse engineer it mostly from your designs given in DDL. Although your DDL is not actually correct, you do very helpfully explicitly say "something like" & "some sort of [...]?". Though it is much better to give fewer words plus DDL, it should *be* DDL with added clear complete sentences re constraints without misrepresenting them by calling them FKs or PKs when they are not. Use enough words, sentences & references to parts of examples. – philipxy May 02 '19 at 05:25
  • PS When giving (base or query result) relation(ship)s/associations or tables say what a row says about the business situation in terms of its column values when it is in the table. [Recent answer of mine giving table predicates, even involving nulls.](https://stackoverflow.com/a/55619046/3404097) (Although in that example as part of being introductory 1 aggregate-valued column is described in terms of another table & a query rather than purely in terms of the situation.) Suggest you try to write the table & constraint predicates for all (yours & others') designs here (& the rest of your life). – philipxy May 02 '19 at 05:56
  • 1
    Avoid preconceptions re what "subtyping" can mean & so when a subtyping idiom can apply. If we have subsets then we have subtyping. Your *business* subsets/types items per the source they are in, sources per the items they hold & sources per the # they are, etc. Any FK involves a subtype. [nvogel's & my subtypes, disjoint & not.](https://stackoverflow.com/a/26093733/3404097) [Ours & your "other must be null" radio-button-FKs design.](https://stackoverflow.com/a/13317463/3404097) Per my 1st comments, this is a case of "FKs to many tables" as antipattern for "subtyping". (*Google.*) Good luck. – philipxy May 02 '19 at 20:20
  • It took a while to get there but now I have come to an understanding of your initial comment and how it applies to this problem I have. Clearly database design is not my area of study but this has been a useful exercise. I will edit my graphics back to text (I thought stack.imgur links were considerd "internal for the purposes of SO" but meh). I have obtained the anti-patterns book and will see what I can learn from it, thank you for your help. I hope that it aids other searchers. – Stephen May 02 '19 at 22:51
  • Good luck. PS Per my text-please comment, your ascii & pretty graphics did not need to be graphics, just use (pseudo-)DDL. You could *add* a pretty picture although I'd argue there is never any more useful content in an ERD than DDL + maybe some comments. There are a zillion ER (with diamonds) & pseudo-ER (only boxes) & ORM (ugh) & other diagram styles. So a pretty picture further needs text or a link for legend! And you *abused* the 2 styles of pretty pictures--as with ascii, don't say wrong things in pursuit of the familiar or pretty. stack.imgur *is* dedicated--for when it's appropriate. – philipxy May 03 '19 at 01:17

1 Answers1

2

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  +
+----------------+
nvogel
  • 24,981
  • 1
  • 44
  • 82