0

I guess this is more of a philosophical question. I have one item with 4 variations. These variations will share about 80% of their columns. The item in question is a portfolio example and the variations are photography/video/web/graphic. They will share column names like client, date, etc.

My question is, should I have 1 table for each variation or 1 base table and an additional table for variation-specific columns. I'm "never" going to need to pull down a photo/web example at the same time or what have you.

I wasn't sure how to Google search for this despite it being fairly basic

Geoff Harper
  • 72
  • 9
  • 1
    Closely related to this question, even if it's not an exact duplicate: https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860 – Bill Karwin Mar 16 '18 at 23:17
  • Thanks @BillKarwin this was really useful – Geoff Harper Mar 16 '18 at 23:24

2 Answers2

1

You have a one-of relationship. The key question is how other tables will reference this entity.

There are three options:

  1. All references are to the parent entity. For instance, all items have price histories, categories, or comments and the references are only to the parent.
  2. All references are to the children. For instance, photos might have objects in them, videos might have lists of producers, and so on.
  3. References can be to either.

What is the answer? (1) suggests that all the rows should be in a single table with a single id shared for all. (2) suggests that the rows should be in a single table for each variation.

(3) is a little more difficult. I would recommend a parent table with the 80% of the columns. Then children tables with the remaining ones. The primary keys of the children tables would be the same as the primary key of the parent (yes, a column can be both a primary key and a foreign key).

For this situation you need to do a little work to ensure that a given parent entity appears in exactly one child table. That can be handled with foreign keys and triggers (in MySQL).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

As long as there are no specific rules for a variant id just create a Media table.

If you decide to keep them seperated i'd expect tables to have a date and client_id.

nitsram
  • 686
  • 5
  • 5
  • What about variation-specific columns? Like feature lists for web or shot type for video/photo. If it was one table wouldn't there be a bunch of null values in each row for the columns that don't apply? Is that even a problem? – Geoff Harper Mar 16 '18 at 23:15
  • Since there are constraints id go ahead and have 4 seperate tables. Use foreign keys and reference tables to ensure your data stays in good shape. if you ever, in the future, have to denormalise then you can do it then. – nitsram Mar 16 '18 at 23:25