1

Let's say I'm tracking information like books, cars, animals and then each of these have a properties for classification like genre (horror, comedy, etc), type (suv, hatchback, etc), class (mammal, etc). My instinct tells me I would have 3 tables like book_genres, car_types, animal_classes to store these.

But would there be a case or an argument for using just 1 classifications table and having 3 fields: id, classification_name, classification_option and just referencing this in my books, cars, and animals tables?

Or even 2 tables: classifications table with id and name (types, genres, classes) and classification_options table with id, classification_id, and name (horror, suv, mammal, etc)

rahc01
  • 47
  • 7
  • 2
    Oh no! Someone else has (re)discov=red the OTLT, One True Lookup Table. It's an anti-pattern. https://oracle-base.com/articles/misc/one-true-lookup-tables-otlt – Walter Mitty Jul 08 '19 at 20:03
  • Time to read a published academic textbook on information modelling, the relational model & DB design. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Dozens of published academic information modeling & DB design textbooks are online free in pdf. stanford.edu has a free online course. (But asking for resources outside SO is off-topic.) PS For when you add more data then just class on a per-class basis: [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) – philipxy Jul 08 '19 at 20:16
  • (Obviously--) This is a faq. Before considering posting please always google any error message & 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. – philipxy Jul 08 '19 at 20:23

2 Answers2

1

Better create a table per each type: say that next year you'll want to add a property/column to car called "make" or "model". By having separate tables for each type you'll be able to add this column only to cars without affecting books and animals.

Keeping the structures modular and specific per type (the principal called: separation of concerns) will make your code in the future easier to read, maintain & test!

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
1

Your instinct tells a right thing: three tables are better from a viewpoint of both logical and physical design; the data integrity is supported by database, and queries are simpler.

However, when your application should track some abstract "items", and users should be able to create their own classifications, the EAV-like design for item properties storage may be better regardless the lack of data integrity which you need to support in the application.

serge
  • 992
  • 5
  • 8