Re: join table design question

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461