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

[download]

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.

In reply to Re: OT - SQL choosing a schema for index tables by chacham
in thread OT - SQL choosing a schema for index tables by bangor

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.