CODEWITHSUNDEEP
databasedatabase-designrelational-databasedatabase-schemadatabase-normalization
Chris
Chris

Reputation: 3846

Database structure: how to best design for this issue?

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?

Upvotes: 0

Views: 282

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Take a look at this model:

enter image description here

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

Upvotes: 1

Joel Brown
Joel Brown

Reputation: 14408

Unless you actually mean that items can belong to multiple objects, using assigned_items is not helpful. If I understand you correctly, your main concern is that you sometimes have images that are for part of an item, so how do you describe the image?

Here is what I suggest:

OBJECT: id, name

ITEM: id, name, quantity, object_id

IMAGE: id, name (null), object_id (null), item_id (null)

If your DBMS supports constraints, have a constraint on IMAGE to enforce one or the other of object_id or item_id (but not both). This allows you to define the image as being either for an item or for the object as a whole.

When you query for the name of an image, you would use the COALESCE function (or your DB's equivalent) to pick up the image override name (if it exists) or the object/item name (if the override doesn't exist).

Upvotes: 1

Related Questions