CODEWITHSUNDEEP
database-designrelational-database
rahc01
rahc01

Reputation: 47

Does using 1 table instead of multiple tables make sense in this case?

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)

Upvotes: 1

Views: 268

Answers (2)

serge
serge

Reputation: 1022

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.

Upvotes: 1

Nir Alfasi
Nir Alfasi

Reputation: 53565

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!

Upvotes: 1

Related Questions