Thread: join table design question

Hi,

I have tried two db designs and both can work but I wonder which one
the db gurus would go with. This is a general db design question
that has cropped up in a few situation. Here is the example I've been
working on...

In an online store, categories and images have a many-to-many relationship and
products and images have a many-to-many relationship. (It may be that
many other tables can have many-to-many relationship with images.) The
db has the basic three
tables

categories
- id
- name

products
- id
- name

images
- id
- filename

What I can't decide on is how to do the join tables.

Option 1 - would have many join tables and each would require a class in my ORM.

categories_images
- category_id
- image_id

images_products
- product_id
- image_id

Option 2 - have a one generic join table and only one join class

image_joins
- owner_class
- owner_id
- image_id

In option 2 a record could be (Category, 2, 3) or (Product, 4, 1)

--------

Option 1 seems simpler but could lead to some repetitive code (at
least in the data base definition).

Option 2 has only one join table and class. It is a little more
complicated and I don't know how it fits
into the normalization stuff.

How do I choose?

Thanks,
Peter

On Sat, Jan 14, 2006 at 04:51:44PM -0700, Peter Michaux wrote:
> Option 1 - would have many join tables and each would require a class in my ORM.
>
> categories_images
> - category_id
> - image_id
>
> images_products
> - product_id
> - image_id
>
> Option 2 - have a one generic join table and only one join class
>
> image_joins
> - owner_class
> - owner_id
> - image_id
>
> In option 2 a record could be (Category, 2, 3) or (Product, 4, 1)
>
> --------
>
> Option 1 seems simpler but could lead to some repetitive code (at
> least in the data base definition).

Aside from being simpler, this option also allows you to use plain-old
RI to handle garbage collection on the many-many tables.

> Option 2 has only one join table and class. It is a little more
> complicated and I don't know how it fits
> into the normalization stuff.

This might be more or less normalized; I don't know. I do know you're
taling the difference between one of the higher forms of normalization,
afaik beyond 2NF.

And with this form you're going to need some repeated code to handle RI
anyway, which you'll now have to roll by hand.

Personally, I'd just go with option 1. If you're worried about the code
repetition you could easily create a plpgsql function that would handle
the DDL for an arbitrary table for you, so you could do:

SELECT create_image_many_many('categories');
SELECT create_image_many_many('products');
...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461