[Dbix-class] Schema design query

Dermot paikkos at googlemail.com
Fri Feb 27 00:11:09 GMT 2009


2009/2/26 Darren Duncan <darren at darrenduncan.net>:
> Dermot wrote:
>>
>> 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?
>
> I think the real answer here depends mainly on how you are treating these
> categories in your program.
>
> If your program is using different logic based on the categories, such as
> the categories might be [users_may_update, entity_represents_user,
> is_primitive_type] etc, and you expect that any addition, changes, or
> removal of categories would have corresponding code logic updates, then you
> may have separate join tables per entity with supplemental information that
> the special logic uses.
>
> On the other hand, if the categories are of the kind that there is no
> special logic needed to handle and they are of the kind that users could
> conceivably add/change/remove, such as a list of human languages or of
> countries or of currencies etc, then you would typically want the separate
> single category and single join table.
>
> Or in the middle, I would say the time you may want multiple join tables is
> if each category denotes different kinds of supplemental details.  For
> example if each category is a media type and the entity is a library
> catalogue item.  Eg, a category of 'book' would have some different details
> than a category of 'video'.
>
> I think you may have to provide more details about your situation if you
> want better help than this.  For example, what the actual entity table
> schema is that you inherited, and clarify what you are allowed to change in
> your schema and what you can't change.


Yes your right. I have not shown my hand completely.

The requirement/situation is this:

The entities are assets. I think a good analogy would be CDs. The CDs
are the entities. The categories are, lets say, music genres.

Currently there is a single search facility that returns (from binary
indexed files outside of the MySQL backend) just the CD_id and a
ranking. The requirement is to be able to filter search results
(CD_id) to within genre's.

A CD will belong to one or more genres. Results that come back from
the search object therefore need to be, quickly and efficiently,
filtered. So if the users search for "symphony"  and selects category
"Rock" and "60s" they would get 'Bitter-Sweet Symphony - Verve' and 'I
hear a Symphony - The Supremes'  but NOT Mozart of anything from the
classic genres.

I hope that is stating what I am trying to unravel clearly. I am
looking for a means to filter results quickly given an entity id.
Thanx again.
Dp.



More information about the DBIx-Class mailing list