0

Given I have 2 tables:

books bookstores

95% of the entries in the books table will be available in ALL bookstores. 5% of the entries in the books table will only be available in a SINGLE bookstore.

Books will NEVER be available to several bookstores, it will always be 1 or all.

I can see two ways of linking these tables:

  1. Place a nullable foreign key on the books table, referencing bookstores. If null, it’s available for all bookstores, if not it’s only available for the referenced bookstore.

  2. Create a linking table which will hold every combination of book / bookstore for the 95% of books and the single book / bookstore combination for the other 5%.

Option 2 is the normalised way of doing this and I guess technically correct, but will result in a lot more DB entries and management.

What do people think is the best approach to take here?

hercule
  • 11
  • 2
  • I would choose the normalised version since you probably cannot guarantee at 100% that your current rule (and cardinality) will never change in the future. – iDevlop Jun 26 '19 at 21:08
  • There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461/266284) – philipxy Jun 26 '19 at 22:29
  • Neither direction of rearranging one of these subtyping/inheritance/polymorhism designs into the other is normalization. Normalization replaces a base table by others that are smaller projections of it. [Does single table inheritance results in denormalization](https://stackoverflow.com/a/53370509/3404097) [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [How to structure a database schema to allow for the “1 in a million” case?](https://stackoverflow.com/a/40374037/3404097) – philipxy Jun 27 '19 at 07:54

0 Answers0