Help in db design

Databases
1

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

2

try writing a query which retrieves categories, brands, and products, each with images

3

but on which table structure: i> or ii> ?

4

do it on both, of course, to compare how complex it is

5

On my view:

Working on 1st: will be easier but it will violate the DRY principle.
Working on 2nd: will be a bit tricky on querying but it can be reused for different table just by using the combination of object_id & taxonomy fields.

6

DRY principle?

7

Dont Repeat Yourself

8

oh, i see, thanks dddougal

i still think 1st approach is better

:slight_smile:

9

I recently tackled the same problem but for addresses and used your second methodology for the same reason.

10

are you referring to Rudy or me?

11

I think I would personally prefer method 1. I’m not exactly sure why, it just seems to be more of a modular design to me. Maybe easier to extend, customize and modify down the road if necessary.

Also it would make your queries easier in general b/c you’re no longer having to worry about separating the images by category, they’re already separated.

I think the DRY method could be focused on having to repeat your query code (WHERE taxonomy=?) in EVERY image query you make. I do NOT think you are repeating yourself by having three similarly structured tables, the data in them will be completely different.

Just my $.02

12

In either ways you have to query the tables exactly that number of times what the total tables are for images but only the table names will be different. So I would prefer the first method which is clear and would definitely be easy to extend and can be implemented Referential Integrity (RI) too. I don’t think you can implement easily RI with second method. So except increasing the number of tables in the database, there will not be anything wrong in first method as far as I know.

13

that’s correct, you cannot declare the FK and relational integrity is down the, um, tubes

:smiley:

14

Your second method.

15

Ok Thanks to all of you for the opinions.
I went with the Method #2 and implemented it.
Reasons:

  • my table types are MyIsam type
  • The same MVC files has been re-used for all the different types of images (just have to pass the ‘taxonomy’ as an additional reference).
  • Its easier for me to add images for other tables.

Nevertheless to say Opinions from your side are always welcome :slight_smile: