Dear monks,
I want to reprisent a chunk of data (probably a BLOB), and a set of dynamic attributes (which might not be the same for each record, perhaps with many different attribute possibilities). I was wondering which of the following methods is preferred for such a system:

Attributes in another table - a new table with the following structure is made: 
create table attributes (
    id int(),        # the ID of data chunk record this attribute appl
+ies to
    attr char(10),   # the attribute name
    value char(10),  # the attribute value
    primary key (id, attr)
);

[download]
The advatage of doing this is mainly that you can look up the attributes more easily, remove and add them in a simple manner, and it's more with the spirit of databases. The downside is that it will probably result in many more reads / seeks / whatever, to load the attributes, than

Attributes as a blob - store some kind of serialized (packed or Storable'd) data structure in another blob, on the same row as the data, which contains the attribute name / data pairs all mushed together. This means that we can't (easily) search by attributes, but loading of the attributes will probably be much quicker. If the attribute set grows large there is more redundance in reading / writing of data, but that probably won't happen.

Which would you guys probably use? Should I consider this based on the database server aswell, or will it not make that much of a difference? Should i not even consider such backwards thinking and simply create a column for each possible attribute? Are there &lsb;dis&rsb;advantages i haven't considered and should? Is it too redundant to hybrid the two? Or is that even more backwards?

Thanks in advance,

-nuffin
zz zZ Z Z #!perl

In reply to Well Designed RDBMS by nothingmuch

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.