Database structure: how to best design for this issue?

Question

I have users that have several objects and can upload images for those objects. Each object has several items. The photos the user upload can be assigned to those items. The thing is, one object can have one specific item more than once.

To give an example: objects are cars and items are seats, windows, doors, etc. A car may have 5 seats, but all seats are the same item. The description of the image should, however, still be "seat 1", "seat 2", etc. and the user can upload multiple images for seat 2 as well.

Till now I have the following tables:

objects: id, name

items: id, name

assigned_items: id, object_id, item_id, quantity

images: id, object_id, item_id

How would you best solve this issue?

The reason I use quantity is, because if type of the item changes, then most probably of all the items. E.g. 4 seats can become 4 wheels, etc. So, if there was a row for each assigned_item, lets say seat1, seat2, seat3, etc, then this would be more difficult to change, no?

Answer 1

Take a look at this model:

It allows you to:

• Connect multiple items to multiple objects (thanks to OBJECT_ITEM table).
• Connect the same item multiple times to the same object (thanks to OBJECT_ITEM.POSITION field).
• Connect multiple images to an object-item connection (thanks to OBJECT_ITEM_IMAGE table). So, we are connecting to a connection, not directly to an item.
• Name the image specific to the object-item connection (thanks to OBJECT_ITEM_IMAGE.IMAGE_NAME field), instead of just specific to the image.
• Ensure image name is unique per object-item connection (thanks to UNIQUE constraint "U1").

NOTE: This model can be simplified in case OBJECT:ITEM relationship is 1:N instead of the M:N, but your own attempted model seems to suggest it is M:N.

NOTE: To connect an image directly to OBJECT (instead of OBJECT_ITEM), you'd need additional link table (OBJECT_IMAGE) in "between" OBJECT and IMAGE.

---

Example data:

OBJECT:
    Car

ITEM:
    Seat

OBJECT_ITEM:
    Car-Seat-1
    Car-Seat-2
    Car-Seat-3
    Car-Seat-4
    Car-Seat-5

OBJECT_ITEM_IMAGE:
    Car-Seat-1-Image1 "Seat1 Image"
    Car-Seat-2-Image1 "Seat2 Image"
    Car-Seat-2-Image2 "Seat2 Alternate Image"
    Car-Seat-3-Image1 "Seat3 Image"
    Car-Seat-4-Image1 "Seat4 Image"
    Car-Seat-5-Image1 "Seat5 Image"

IMAGE:
    Image1
    Image2