[Dbix-class] Schema design query

Dermot paikkos at googlemail.com
Thu Feb 26 23:04:08 GMT 2009


2009/2/26 Darren Duncan <darren at darrenduncan.net>:
> 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.
>>>
>>> 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

This is a new requirement to an existing system. I have nothing at the
moment but an existing entity table. I imagine the 1st phase will be
to create a categories table with 2 columns, cat_id and
cat_description. The point you have made is about a single join table
for 2 categories and this drives at the heart of the problem I am
trying to unravel. Is is 'wise' or indeed possible to create a single
join table that will map entities to 1 or more categories.

To summarize; should I create 7 join tables, each with entity_id and
the category_id, or can create a single table with the entity_id and 7
(boolean) categories?

Thanx,
Dp.



More information about the DBIx-Class mailing list