[Dbix-class] Schema design query

[Darren Duncan]

Dermot wrote:
> 2009/2/25 Rob Kinyon <rob.kinyon at gmail.com>:
>> On Wed, Feb 25, 2009 at 15:58, Dermot <paikkos at googlemail.com> wrote:
>>> 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.

Do you have a table of categories, where there is one record per category?  If 
so, then you don't have any (is_cat1...) fields in your entity table, but rather 
you have a single join table between that categories table and your entities 
table, which has one record per each category that each entity is in; that join 
table usually has 2 columns, naming the category and entity it applies to. -- 
Darren Duncan