[Dbix-class] Schema design query

[Rob Kinyon]

This requirement would have a cds table, a genres table, and a
many-to-many join table. That join table would have ( cd_id, genre_id
). Look up many_to_many as a relationship.

Rob

On Thu, Feb 26, 2009 at 19:11, Dermot <paikkos at googlemail.com> wrote:
> 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.
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- 
Thanks,
Rob Kinyon