Suggested database design for columns that are usually empty

Question

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.

If you want a normalized table layout, your proposal here would be correct. If you want the academic reasoning: http://en.wikipedia.org/wiki/Database_normalization#Normal_forms — Nathaniel Ford, Aug 24 '12 at 18:46

score 3 · Accepted Answer · answered Aug 24 '12 at 18:56

3

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.

answered Aug 24 '12 at 18:56

invertedSpear

10,864
5
39
77

Right...we don't want to have to start adding 'giraffe_additional', 'rhino_additional', 'squirrel_additional', etc. – Gerrat Aug 24 '12 at 18:59
But what data type does "Value" have? – podiluska Aug 24 '12 at 19:01
would think varchar(whatever max length you need) since it's the most general data type, you can cast it in your query if you need to, but unless you are averaging IQ, I don't see many cases where you would need anything but a string. – invertedSpear Aug 24 '12 at 19:32
@invertedSpear Personally, I don't approve. But it's not my database :) – podiluska Aug 24 '12 at 19:59
I understand, but as you said, a lot of the decision comes down to how it's used. Personally I'm more about having a flexible solution, even if it takes up extra disk space. It would take me a lot more time to re-factor code if I have to change the schema than it would be worth to have it more structured. – invertedSpear Aug 24 '12 at 21:06

score 2 · Answer 2 · answered Aug 24 '12 at 18:50

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 :)

score 1 · Answer 3 · edited May 23 '17 at 12:27

1

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

edited May 23 '17 at 12:27

Community

1
1

answered Aug 24 '12 at 19:03

Walter Mitty

18,205
2
28
58

score 0 · Answer 4 · answered Aug 24 '12 at 18:53

0

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.

answered Aug 24 '12 at 18:53

Icarus

63,293
14
100
115

Agreed; although different database platforms have different storage regimes - I don't know enough about mysql to say how it affects this particular platform – podiluska Aug 24 '12 at 18:55

Suggested database design for columns that are usually empty

4 Answers4