in reply to OT - SQL choosing a schema for index tables
This is similar to an entity with unknown attributes, to which the usual solution is an Entity-Attribute-Value table, similar to your second option.
EAVs are feared for their slowness. However, in many cases, it is simply not true, as they perform quite well. Further, in fancier RDBMSs, the table can be partitioned, which works well.
Ids are used when there is no natural primary key. (So many people use surrogate keys out of sheer laziness, ignorance, or stupidity.) Though, assuming the only unique identification of the tree is that it is noted in the database, id is fine. This would leave 2 tables. One for the tree, the other the EAV. Also, assuming the entire database or schema is tree related, "tree" becomes redundant, unless related to the main table:
CREATE TABLE Tree ( Id INT PRIMARY KEY, Owner Nickname Location .... ); CREATE TABLE Tree_Attribute ( Tree REFERENCES Tree, Attribute VARCHAR(020), Value VARCHAR(100), PRIMARY KEY(Tree, Attribute, Value) );
Depending on the RDBMS, you may not be able to use the word value. Also, you might be required to define Tree in the second table. If allowed, however, this allows for better coding. Of course, you can name the keys, to make error messages clearer, though that is a matter of preference.
If attributes are not arbitrary, will contain a description, or will be chosen from a drop down or the like, you can add a lookup table for attribute as well, and FK the EAV to it accordingly.
Update: On second thought, i was reading this as if the attributes were unknown. If they are known, a separate table per attribute would be advisable.
|
|---|