[Dbix-class] Schema design query

Dermot paikkos at googlemail.com
Thu Feb 26 21:15:03 GMT 2009


2009/2/25 Rob Kinyon <rob.kinyon at gmail.com>:
> On Wed, Feb 25, 2009 at 15:58, Dermot <paikkos at googlemail.com> wrote:
>> Hi,
>>
>> I am trying to design a schema and I could use some ideas as I'm not
>> confident about the best way forward.
>>
>> I have a table of entities. Each entity is to belong to at least one
>> or more of 7 different categories. The requirement is to retrieve
>> entities by category. As far as I can tell, I either include a new
>> field in the entities table (is_cat1, is_cat2...etc) or I create one,
>> or possibly, more join tables on the entities and their category. The
>> other issue is that user's search the entities table via a web
>> front-end search facility (not from the DB) that returns the entity
>> id,  so I am trying to find a structure that, given a entity_id, can
>> be filtered on relationship to the categories.
>
> The "solution" is to use a many-to-many relationship. This is
> well-supported by DBIC.

Reading between the lines; your suggesting a single join table with
the entity_id and all the other 7 categories or,  7 individual join
tables with entity_id/category_id(s). That's that bit I am not
confident about. I have to implement this in a production environment
(with a tight dead-line) so I'm fishing for advice. I might be accused
of be lazy because I should profile/benchmark both options but I guess
I am hoping someone might have a good enough understanding of DB
schema optimisation to tell me not to bother with one option. That's
the benefit of experience, unfortunately, I don't have that.
Thanx,
Dp.



More information about the DBIx-Class mailing list