2

I have a requirement to design a table which almost looks like a 3 dimensional requirement.

For example,

  1. There are "infinite" varieties of Dinosaurs.
  2. All the Dinosaurs will have some common set of features (name, years since existence on earth, weight, family name, etc.,).
  3. There will be some specific features which are sometimes unrelated to each variety. Each such feature is identified with a "name"="value" pair. The "name" is something like the name of a feature (like may be horns on head, the voice frequency etc) and the "value" could be the value of the feature (like 3 horns on the head, some 10hz voice frequency etc.,).

As per the requirement we can create a table X with common features like columns referring to cid, name, years since existence, weight etc.,. And there there could be another table Y with just columns (uid, name, value) where cid could be bound used for the uid.

But the fact that, the number of rows in X would be enormous (trillion+, we might not have so many dinosaur families, but that is just an example). Similarly, the name=value would be several hundreds per entry in table X. This would increase the number of rows in table Y.

Since, the feature "name" is also "infinite", we cannot create a table with fixed number of columns, like column per feature unfortunately because that would be like dynamic columns.

One idea would be to have two more columns in table X, where one column can consist of all the features ("name") as comma separated and the other column can consist of all the "values" in comma separated. But I know this is a very bad design.

Could there be any better solutions for this requirement?

gbn
  • 422,506
  • 82
  • 585
  • 676
user1093110
  • 21
  • 1
  • EAV: http://en.wikipedia.org/wiki/Entity-attribute-value_model Have fun... – gbn Dec 13 '11 at 09:19
  • When you say a "dinosaur", do you mean "dinosaur species" or "dinosaur specimen" (i.e. individual fossil)? Do all specimens of the same species share the same set of properties? – Branko Dimitrijevic Dec 14 '11 at 03:42

1 Answers1

1

Have a look at below example. You specify the species of dinosaurs in dinosaur_type, the individual dinosaur go in table dinosaur and are linked to a species/type and then in dinosaur_feature you can add rows for each individual dinosaur to add any kind of (infinite) possible feature for a dinosaur..

Table `dinosaur`:
id 
dinosaur_type_id (fk)
name
years since existence on earth
weight
family name

Table `dinosaur_type`:
id
name

Table `dinosaur_feature`:
dinosaur_id (fk)
feature_name
feature_value
Richard
  • 4,341
  • 5
  • 35
  • 55
  • 1
    This is much better than a huge varchar filled with name=value pairs. I would probably suggest trying to normalise your "feature_name" too, but if they are truly arbitrary and endless, it might not be possible. – Cylindric Dec 13 '11 at 10:10
  • This is an EAV structure that has name-value pairs in the `dinosaur_feature` table – gbn Dec 13 '11 at 14:22