CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
David542
David542

Reputation: 110502

Suggested database design for columns that are usually empty

I have a table with four fields that are usually filled in:

`animal`
- id
- type
- name
- weight
- location

Three additional fields are filled in if the animal type = 'person'. This happens about 5% of the time. The additional table would be:

`person_additional`
- animal_id (FK)
- IQ
- native_language
- handedness

Is the suggested practice in db design to store this in two tables or one table? It almost makes no difference to me, but I was curious about best practices and why one would be preferable over the other.

Upvotes: 1

Views: 136

Answers (4)

Walter Mitty
Walter Mitty

Reputation: 18950

Normalization issues aside. Animal and person are an instance of the pattern called generalization specialization, or gen-spec for short. The design of relational tables for cases of gen-spec has been covered in other questions. Do a search on "class table hierarchy" in SO.

Example: Table design and class hierarchies

Upvotes: 1

invertedSpear
invertedSpear

Reputation: 11054

Two tables is probably the right approach, but I might suggest a different second table. I would define it as:

`animal_additional`
- animal_id (FK)
- Trait (this would enumerate allowable traits)
- value

This would give you more flexibility in having different traits for different types, or even different traits for the same type.

Upvotes: 3

Icarus
Icarus

Reputation: 63970

One additional good reason to split this into 2 tables is that by having everything in one table, the amount of space required to store one row will increase unnecessarily since most of the time your columns will be empty but the database still has to allocate certain amount of bytes for every row.

Splitting into 2 tables, makes more efficient use of hard drive space.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

If you were to store them in the same table, then that would effectively be a multivalued dependency; a violation of 4th Normal Form, so from a purist point of view, separate tables is better.

Also, what happens if another kind of animal is added that requires different kinds of supplementary fields - if all your data were in one table, then eventually, you'd have a bunch of different fields for different purposes.

From a practical point of view, it depends on how the data is used, etc;

From a pedantic point of view, other animals have handedness :)

Upvotes: 2

Related Questions