in reply to Well Designed RDBMS

Would you ever want to do SELECTs based on those fields? If so, you shouldn't put them in the BLOB. Say you had field 'foo' inside the blob. How do you write a SELECT statement to get it out? You can use LIKE clause, but that makes the database do a lot more work, and you'll probably get a some stuff back that you don't care about (depending on the exact structure of the data).

Try to make seperate tables with similar data and JOIN them. That way, you don't waste space on fields you rarely use.

Remember, the 'R' stands for 'Relational'. Don't treat an RDBMS like a flat-file with a weird API.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

Replies are listed 'Best First'.
Re: Re: Well Designed RDBMS
by nothingmuch (Priest) on Apr 07, 2003 at 14:16 UTC
    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
[reply]
      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]
      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]

In Section Seekers of Perl Wisdom