[Dbix-class] Schema design query

[Darren Duncan]

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