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_idimages_products
- product_id
- image_idOption 2 - have a one generic join table and only one join class
image_joins
- owner_class
- owner_id
- image_idIn 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