Re: RFC: How to name it? DBIx::Table::Denormalized, DBIx::Table::Dynamic, ...

Why not simply create a table "properties", with three fields: id, key and value?

File     Property
.id      .id
.name    .file
.size    .key
.type    .value
[download]

You can decide yourself which propertise are important enough for in the File table, but I'd just put almost all in Property, for ease of programming:

File     Property
.id      .id
.name    .file
.type    .key
.size    .value
.parent

File
id   name   type   size   parent 
1    ROOT   DIR    NULL   NULL
42   foo    FILE   123    1

Property
id   file   key            value 
9    42     content_type   image/x-xyzzy
10   42     width          1024
11   42     height         768
13   42     bpp            16
[download]

This, with the glue to make it automatic in Perl (which you could even do with AUTOLOAD), seems much easier to me, and can work with non-mysql databases.

Growing tables horizontally automatically is a crime, as much as having indexes in table or column names is.

Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

Comment on Re: RFC: How to name it? DBIx::Table::Denormalized, DBIx::Table::Dynamic, ... Select or Download Code

Replies are listed 'Best First'.
Re^2: RFC: How to name it? DBIx::Table::Denormalized, DBIx::Table::Dynamic, ... by Corion (Patriarch) on Sep 15, 2005 at 15:29 UTC
I went that way already, and while the flexibility is really nice, the performance and programmatic handling are both abysmal. Also, implementing EAV (entity, attribute, value) in a relational database seems somehow wrong :) I also thought of mixing the two approaches, but that can happen later on. The code is currently only geared towards SQLite and not MySQL (which I mostly avoid), but I don't know of problems with other databases.	[reply]
Re^3: RFC: How to name it? DBIx::Table::Denormalized, DBIx::Table::Dynamic, ... by Juerd (Abbot) on Sep 15, 2005 at 18:51 UTC
I'm using alike setups in several places, without abysmal performance. I do wonder what we do differently. This is the classic approach, used by many. Did you perhaps forget the index on Property.file? That's kind of important to have. Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }	[reply]
Re^4: RFC: How to name it? DBIx::Table::Denormalized, DBIx::Table::Dynamic, ... by Corion (Patriarch) on Sep 15, 2005 at 21:34 UTC
The problem is not so much with the table as with the queries I ran against the table, which needed many joins of the properties table with itself, and I found that these were really slow and also cumbersome to work with. On the other hand this was before I started using SQL::Abstract.	[reply]