Thoughts, Advice on my Table and Relationship Schema for Images

[dmontano]

Hi all,

Hoping to get your thoughts and advice on my attempt at modeling the tables and relationships to support the following:

1. I have many different tables like PERSON, PRODUCT, FACILITY, etc. where each record will have many images. For example:

• A PERSON (John Doe) will have many images that depict John Doe.
• A PRODUCT (XYZ 500) will have many images that depict XYZ 500. 
• A FACILITY (Wilson Convention Center) will have many images that depict Wilson Convention Center.
• Etc.

2. Since I need a separate table to hold the "many" images for each of the tables above - it seems like a SINGLE table that can hold ALL images would be efficient instead of tables for each PERSON IMAGE, PRODUCT IMAGE, FACILITY IMAGE, etc.

3. Since it "might" be possible that two different tables, such as PRODUCT and EQUIPMENT could share the same image - a JOIN table would solve such situation. 

NOTE: I am also considering changing the table currently name IMAGE in the schema presented in the attachment to the name DOCUMENT. The reason behind the name change is to allow the table to store any DOCUMENT such as: IMAGES, SPREADSHEETS, PDFS, etc. Using DOCUMENTS in this manner would certainly justify more the creation of a JOIN table. In this schema, more than one product could share the same WARRANTY document, Installation Guide, etc.

• Is the attached schema diagram a reasonable approach to this situation?
• Are there any problems in this approach?
• Is there a better way to do this?

As always, I appreciate your input.

image_join_concept.pdf

[rwoods]

My only concern here is that the JOIN table doesn't know whether it represents an instance of a DOCUMENT being joined to a person, a product or a facility.

If you ever need to show a layout based on the JOIN table, it would be helpful to have a field in the JOIN table that indicates which type of entity it is linked to. You may wish to display the entity 'name' (which could be a person's name, a product name, or a facility name), and you'll need to know which relationship to query to pull that information.

Otherwise, I think this solution is a proper use of relational database design, and should be very flexible in the future.

[comment]

4 hours ago, dmontano said:

it seems like a SINGLE table that can hold ALL images would be efficient

Yes. Especially since the same image can belong to more than one entity, it makes sense to keep them all in the same table.

 

4 hours ago, dmontano said:

Since it "might" be possible that two different tables, such as PRODUCT and EQUIPMENT could share the same image - a JOIN table would solve such situation. 

I don't see the need for a join table, unless you foresee a situation where the same image can belong to more than one person, or more than one product, or more than one facility.

For the described situation, where an image belongs to one person and/or one product and/or one facility, it would be sufficient to have 3 foreign key fields in the Images table. Unless I am missing some reporting need that such structure would not cover.

--
Note also that in your proposed arrangement you would need to make sure that the primary keys of the 3 tables are unique not only in their own table, but also within the 3 tables as a whole.

 

[bcooney]

Either use uuids or add another predicate to document::Entity, such as entityTypeId

[dmontano]

21 hours ago, rwoods said:

My only concern here is that the JOIN table doesn't know whether it represents an instance of a DOCUMENT being joined to a person, a product or a facility.

Thank you for your reply. I am glad to hear that you opinion is I have not botched this up.

Since I am able to record the PERSON, PRODUCT, FACILITY, etc. as a foreign key in the join table - I am currently able to display the "name" of any of the PERSON, PRODUCT, FACILITY tables by:

1. Create (in this example) one pop-up field for each of the three tables - PERSON, PRODUCT, FACILITY - of the "entity_id_fk" field from the join table on the join layout.

2. Each of these "entity_id_fk" pop-up fields is specified as using "ID" and display using field as "name".

3. I then use a calculation to show that field when it's field "not IsEmpty"

4. Then I stack them all on top of one another.

I have used this method for STATUS and DEFINITION in my database - but those did NOT have a need for a join table. Then I was worried that even those where being built in a manner that might be a problem. I decided to finally ask as soon as I saw a join table was needed before I went to far down the road.

Thank you for your time!

21 hours ago, comment said:

I don't see the need for a join table, unless you foresee a situation where the same image can belong to more than one person, or more than one product, or more than one facility.

Hi Comment,

Thank you for your input!

I am changing IMAGE to DOCUMENT. My view "now" is I can treat an IMAGE as a DOCUMENT. This generalization will allow me to store a spreadsheet, a photograph, an illustration, a guide, a invoice, etc. in one table. I will include a field to assign what "type" of document each are.

With this in mind, I believe a join table is warranted: a PRODUCT name XYZ 500 and XYZ 600 may use the exact same warranty DOCUMENT. They may use the exact same installation guide DOCUMENT. They may even use the same photograph as the differences are hidden on a circuit board, etc.

I can foresee PRODUCTS sharing the same document. I have a harder time imagining two different people using the same document - but it would not surprise me.

[dmontano]

9 hours ago, bcooney said:

Either use uuids or add another predicate to document::Entity, such as entityTypeId

Hi Bcooney,

Thank you for your input.

I actually use an "entity_type_id_pk" in each of my tables throughout the database to allow me to connect to other tables via a "entity_type_id_fk" for purposes just like this situation. Using these constructs allow me to display in IMAGE JOIN which "table" it is coming from.

Please note: I am changing the table names in order to generalize things a bit more, for example: IMAGE is changing to DOCUMENT.

[comment]

2 hours ago, dmontano said:

PRODUCT name XYZ 500 and XYZ 600 may use the exact same warranty DOCUMENT.

Then you do need a join table.

I still think having 3 foreign key fields (in the join table, with only one of them populated) would be the simplest arrangement (I might change my mind if there are some potential future entities that could also own documents).

 

 

Edited August 15, 2024 by comment