Imagers for a product in Internet Store — one to many or many to many?

Question

I'm creating a database schema for a internet store and I wonder how to better create the tables "images" and "products". A simpler way will be to have it as one_to_many, that is:

 images(....., product_id references products(id)) 

The more flexible way is to create it as many_to_many. However, I can't think of any scenario where I'll really need this products <-> many_to_many <-> images relationship, that is, where I'll reuse images for more that one product.

What would you recommend? Is there anything I'm missing? When will I need many_to_many in my case?

Answer 1

I think that is 1:N is fine if it works for your domain.

Here are some things to think about. If you use 1:N and need to share a picture or image file, then you'll end up with multiple entries in the images table. This may or may not involve duplicated image files depending on other details. Neither of these is the end of the world.

Still, here are some examples of where images might be shared:

• automotive part descriptions often involve a complex diagram, having many numbered components. A part (that is individually for sale) will be identified with item #14 on some diagram that is intended to be shared across all the other products on this diagram.

• if you allow placeholder images

• the mfgr's company logo uploaded as an image may apply to a larger number of products.

• product groups, subproducts, and categories may introduce shared image usages, but perhaps, such groupings would be (in a) separate (table) from the products in the products table itself.