in reply to Re: Well Designed RDBMS
in thread Well Designed RDBMS

Selecting will probably more oriented toward predetermined records, but at times i may want to retreive by attribute. I'm worried that the occasional lookup by an attribute will not justify two selects, one for the data and one for the attributes. The question - i guess - is which is more of a penalty - using LIKE or selecting again if i need the attributes... I think you both answered my question... =)

-nuffin
zz zZ Z Z #!perl

Replies are listed 'Best First'.
Re: Re: Re: Well Designed RDBMS
by Improv (Pilgrim) on Apr 07, 2003 at 14:30 UTC
    You might also find helpful doing a 2-table query if you really want to avoid doing them seperately. Here's a truncated example with postgres, with appropriate table definitions.
    Read more... (2 kB)
    Hope this helps
[reply]
[d/l]
Re^3: Well Designed RDBMS
by tall_man (Parson) on Apr 07, 2003 at 19:00 UTC
    A well-designed relational database should be normalized, and most of these examples are not because they mix different types of information in the same table. You also are forcing all attributes to be coerced to strings. Here is another approach (supposing Color and Size are attributes some blobs have and not others). You can add as many attributes as you like by adding tables. 
    Blob
blob_id    INT (primary key)
blob_data  BLOB

Color
blob_id    INT (foreign key to Blob)
color_value  COLOR

Size
blob_id    INT (foreign key to Blob)
size_value FLOAT

    [download]
    Searching on attributes (especially single attribute values) should actually be more efficient this way. There's a single type of index and an easy JOIN operation (which you should let the DBMS do for you instead of making two queries).
[reply]
[d/l]
      I'm a wee bit scared of adding a gazzillion tables, as attributes may pushing tens in their types, but i guess this is similar to creating a key value pair of attributes, at least in how different it is from creating a blob object.

      Right?

      -nuffin
      zz zZ Z Z #!perl
[reply]
        tall_man's warning about the risk of spelling errors in attribute names is worth heeding. If your potential range of attributes is in the tens (or dozens), you could have a single attribute table keyed by blob_id, with a separate column (suitably named) for each possible attribute.

        If you need to add more attributes as time goes by -- and if you don't mind the slight extra effort needed each time you add one -- you can alter your attribute table at any time by just adding columns.

        This way, you have just two tables, and doing a join-type query is really simple: "select blob_table.data from blob_table, blob_attrib where blob_table.id = blob_attrib.id and blob_attrib.color = 'blue' [and blob_attrib.flavor = 'grape' and ...]" ...whatever.

[reply]
        Yes, I would say that it's closer to your first approach (attributes in a key/value table) than to your second (attributes in a blob you have to decode yourself).

        Another couple of reasons that completely separate tables might be better: there is no redundant attribute-key field to store, and you avoid attribute-key spelling errors (like "color" vs. "colour"). If the attributes are entered through a user interface, how are you going to check for valid attribute names?

[reply]

In Section Seekers of Perl Wisdom