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?

Joel Brown · Answer 1 · 2012-03-18T14:38:18.467

1

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).

edited Mar 18 '12 at 14:38

answered Mar 18 '12 at 11:19

Joel Brown

14,123
4
52
64

thank you for your answer... what I don't understand is, what if an image is not assigned to an item but to the object itself? there is no relation to the object in images, is there? as if there is no item, I cannot assign the image to anything... – Chris Mar 18 '12 at 11:24
@Chris - I've updated my answer to accomodate the additional information in your comment. – Joel Brown Mar 18 '12 at 14:38
yes, this is a good idea, joel.. however, now you have object_id in item and in image... this is exactly the point why I did not want to use it.. thank you however, for you effort! :) – Chris Mar 18 '12 at 18:27
@Chris - I don't understand what your concern is. You need object_id in ITEM because there is no way to know what items belong to which objects otherwise. You have both item_id and object_id in IMAGE, but you only use one or the other, depending on what the image applies to. There is no way to represent the information that you claim to need with fewer columns or less redundancy. Please explain why you think my answer is problematic. The answer you accepted is more complicated and prone to error than what I'm suggesting. Please tell me how seats can apply to multiple cars? – Joel Brown Mar 18 '12 at 20:44
If you have object_id in items and in image, it does not seem to be completely redundant to me. But I guess what Branko meant is an additional table with those relations. I liked the solution, because now I have IMAGES: id, assigned_to (e.g. 0=object, 1=item), assigned_id (either object_id or item_id, depending on assigned_to value). The other tables then are OBJECTS, ITEMS and ITEMS_ASSIGNED (relation between ITEMS and OBJECTS, as objects can have any number of (and also multiple) items)... what is your opinion on this? anyway, +1 for all your effort! thank you again! – Chris Mar 18 '12 at 20:54
@Chris - Thanks for the +1. The reason I don't like ITEMS_ASSIGNED is that it not only lets an object have many items, it also lets an item be shared by many objects. Based on the example you gave of what an object and what an item might be, that doesn't make sense to me. Can a seat _really_ belong to different cars? I wouldn't think so. Also, OBJECT_ITEM_IMAGE doesn't allow you to have an image of the whole object since item_id is part of the PK, so it is not possible to have a null for item_id. You _can_ have three pictures of "Chair #2" but you _can't_ have one of "chairs 3 & 4". – Joel Brown Mar 19 '12 at 01:09
@Chris - Sorry, ran out of characters and space and in my haste I edited down too much. What I needed to add regarding OBJECT_ITEM_IMAGE is that, of course you can have a picture of multiple items, all the items in an object, and even items in multiple objects - which is a problem, isn't it? However, having more than one item in a picture requires multiple records, which I think you wanted to avoid as much as possible. In that way Branko's solution meets one need while missing a few others. – Joel Brown Mar 19 '12 at 11:45
Well, you are right Joel... you could not have an image of chairs 3&4, did not even think about this, though it would be very rare it could happen, you are right and would not be handled very well. On the other hand, a seat can and usually does belong to multiple cars. It is like the product "seat xy" is used in car1 and in car5. This is good, as there are features assigned to this seat (e.g. seat xy has heating) and maybe in 2 years the "seat xy" will have new features, too, which are then automatically assigned to car1 and car5 as well. – Chris Mar 19 '12 at 12:18

Branko Dimitrijevic · Accepted Answer · 2012-03-18T21:45:12.647

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

Database structure: how to best design for this issue?

2 Answers2