Correct way to implement n-to-m relationship
this is part of 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
Items can have more than one image, so I have an additional Image table and mapping those images to items. Now I see a problem with this structure. Before I can add Images, I have to enter an element.
My question is now. Is this structure a good way to solve my problem with many images / tags for one element?
thanks
a source to share
Yes, that's how it is done. For this purpose, a perfectly normalized database structure.
Depending on your application, some maladjustment may be required at some point to help performance, but I would wait with this until you identify the actual bottleneck.
Make sure you have indexes on all foreign keys and any other fields that you would normally filter. Creating composite indexes on ItemTag
and ItemImage
may also be a good idea.
a source to share
This is a good structure. A mapping table is usually the simplest way to implement many, many relationships.
You can add images to the image table without an element. If you create an element by adding images to it, then you have a choice of how to implement it:
- Create the object and associated image objects in memory as objects that are not saved when edited by the user. The item and its images are saved when the user is done.
- The user first adds images and then creates an item from this set of images.
- Create a new item (with a new ID) and create it as the user creates it. If the user decides to cancel, you will have to delete the item. You can also delete images if they were created for this item only.
I would go for (1) - maybe more work for you, but you end up with a cleaner and more consistent system as a result.
a source to share
My question is now. Is this structure a good way to solve my problem with many images / tags for one element?
The many-to-many relationship adds a lot of complexity to the application. I would revisit if you really need this complexity. For example, you can simply store the image information in a table of elements. Path duplication is not that expensive.
a source to share