0

I like the entity-attribute-value thing because I can add new fields and have the rows automatically removed when the foreign table row is removed, but I don't like the fact that I can't enforce a data type. And the select queries are complicated.

Are there better ways that don't involve creating a table for each attribute?

If I create a very big table with every possible attribute, will this table take up space even if most rows will have NULL on most columns?

thelolcat
  • 10,995
  • 21
  • 60
  • 102

1 Answers1

1

You can enforce data types in an EAV model by using multiple value fields. This gets a bit tricky because you need another column to specify the type and then additional constraints to specify that only one value is filled and that it matches the type.

In most databases, you can handle this using check constraints.

In addition, you can use just a single string value and then enforce contents of the string using check constraints. This is often sufficient. Such constraints make good use of regular expressions in databases that support them.

As for your second question. Each row is going to occupy space for the entity/attribute columns. Whether or not the NULL value occupies any space depends on the database, but this space would typically be small.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! I did more search and I found another way - subtypes - http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database/3579462 . It requires modifying the schema, but at least I can keep the same ID across all tables. Do you think it's a good alternative to the eav? – thelolcat May 15 '16 at 18:59
  • @thelolcat . . . The better solution depends on the "requirements". If you are using Postgres, you might also consider table inheritance. – Gordon Linoff May 16 '16 at 02:03