CODEWITHSUNDEEP
databasedatabase-design
Rich Gallops
Rich Gallops

Reputation: 31

Multiple one to many relationship design

At the moment, we have this design for storing objects with multiple image/video URLs:

tblCompany:
pkCompanyId

tblPerson:
pkPersonId

tblImage:
pkImageId
ImageUrl
fkCompanyId
fkPersonId

While this design handles:

  1. A company having multiple images
  2. A person having multiple images

I can't help feeling that there is a problem with this design as rows in tblImage will have tons of NULL values for the foreign key columns.

Is there a better design? More objects (some unrelated to company or person, some related to company or person) in the design will have images so with the current design tblImage could have more and more foreign keys.

Upvotes: 3

Views: 1825

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

This is actually a pretty good design for just 2 entities that can have images. Yes, you'll have plenty of NULLs, but alternatives (such as separate image tables, or specially crafted 1:N link tables) will have their problems too.

Since this is a 1:N relationship, we don't need any additional M:N junction/link tables.

In case you need to add more kinds of entities that can have images, you could consider inheritance, like this:

enter image description here

This way, an image will automatically be able to connect to any entity that inherits from the tblCommon, no matter how many kinds of entities there are. Unfortunately, inheritance is not directly supported in relational DBMSes, so you'll have to emulate it in one of 3 ways, each with its own set of compromises.

Upvotes: 3

Abhinav Sarkar
Abhinav Sarkar

Reputation: 23792

If I understand the schema correctly, Company and Person are unrelated and both can have one or more images. Then you could just have a table for images themselves and two different tables for company to image and person to image mappings.

tblCompany: pkCompanyId
tblPerson: pkPersonId    
tblImage: pkImageId ImageUrl    
tblPersonImage: fkImageId fkPersonId    
tblCompanyImage: fkImageId fkCompanyId

This schema also enables you to associate an image to other kind of entities too in future (like a Product).

Upvotes: 1

Related Questions