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.
-- Darren Duncan
_______________________________________________
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/[email protected]
