Right way to implement a n-to-m relation

Question

Hello, this is a part from my database structure:

Table: Item
Columns: ItemID, Title, Content, Price

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

Table: Image
Columns: ImageID, Path, Size, UploadDate

Table: ItemImage
Columns: ItemID, ImageID

The items can have more than one image so i have a extra table "Image" and map this images to an items. I see now a problem with this structure. Before i can add Images i must enter an item.

My question is now. Is this structure a good way to solve my problem with many images / tags for one item?

Thank you

Answer 1

Yes, this is how it is commonly done. Perfectly normalized DB structure for that purpose.

Depending on your application, some kind of de-normalization might be required at some point to help performance, but I would wait with that until you have identified an actual bottleneck.

Make sure you have indexes on all foreign keys and any other fields you commonly filter on. Making composite indexes on ItemTag and ItemImage could also be a good idea.

Answer 2

Yeah that works.

In your setup:

Item has many Images

Item has and belongs to many Tags

Image has one Item

Tags has and belongs to many Item

Answer 3

This is a good structure. A mapping table is usually the simplest way to implement many to many relations.

You can add images freely to the Image table without an item. If you are building an item, adding images to it, then you have some choices on how to implement this:

1. Create the item and associated image objects in memory as non-persisted objects while the user is editing. The item and it's images are persisted when the user is done.
2. Have the user add images first, and then create the item from this set of images.
3. Create a new item (with a new id) and build that as the user is constructing. If the user chooses to cancel, then you'll have to delete the item. YOu may also have to delete images if they were created just for this item.

I would go for (1) - possibly more work for you but you get a more understandable and consistent system as a result.

Answer 4

My question is now. Is this structure a good way to solve my problem with many images / tags for one item?

Many-to-many relations add a large amount of complexity to an application. I would reconsider if you really need that amount of complexity. For example, you could just store the image information in the item table. Duplicating a path is not that expensive.