help in db design

[PHPycho]

Hello forums!!

I am in dilema regarding DB design, so I am expecting the forumians help here.
I have 3-4 tables(say categories, brands, products, etc.) and each table has images.
I would like to design db for images for all those tables.

1> My first Approach:
----------------
category_images
----------------
- id
- category_id
- image_title
- image_path
- is_active
- ordering
----------------

----------------
brand_images
----------------
- id
- brand_id
- image_title
- image_path
- is_active
- ordering
----------------

----------------
product_images
----------------
- id
- product_id
- image_title
- image_path
- is_active
- ordering
----------------

Note: all the table have similar structure

2> 2nd Approach
(wordpress like taxonomy concept)
------------------
taxonomy_images
------------------
- id
- taxonomy
- object_id
- image_title
- image_path
- is_active
- ordering
------------------
where,
taxonomy = category or brand or product
object_id = category_id or brand_id or product_id

And query is done as:
SELECT * FROM taxonomy_images WHERE taxonomy=? AND object_id=? ORDER BY ordering

I want opinions from you to choose the appropriate design (for long run)
or can suggest some alternatives too.

Thanks

[Old Pedant]

Well, you can't use the DB to enforce referential integrity with the taxonomy approach. There's no way to tell a DB engine that "object_id refers to the table designated by the taxonomy field."

If you are using MyISAM, I suppose that's no issue, since it doesn't support referential integrity, anyway.

But I'd still say "yuck".

It's *mildly* ugly, but you could do
CREATE TABLE images (
product_id INT NULL FOREIGN KEY products(product_id),
category_id INT NULL FOREIGN KEY products(category_id),
brand_id INT NULL FOREIGN KEY products(brand_id),
... other fields ...
);

It's funky, but even allows for the possibility for one image being used for two purposes (e.g., for product image and category image?).

I suppose one could argue that the correct way to do it is
TABLE: images
image_id INT PRIMARY KEY,
... other info ...

TABLE: product_images
product_id INT FOREIGN KEY products(product_id),
image_id INT FOREIGN KEY images(image_id)

TABLE: category_images
category_id INT FOREIGN KEY categories(category_id),
image_id INT FOREIGN KEY images(image_id)

TABLE: brand_images
brand_id INT FOREIGN KEY brands(brand_id),
image_id INT FOREIGN KEY images(image_id)


That's all properly normalilzed. No NULL references. And still allows you to treat all images in the same way. So that searches for images by name, by path, by size (if you stored the size in the table), etc., etc., can proceed properly independent of the other tables.