3

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.

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
Rich Gallops
  • 31
  • 2
  • Will an image always have a company and a person or just one or a mixture? – mmmmmm Sep 18 '12 at 10:44
  • An image will always have one foreign key reference (one or the other). To support other objects in the design with images we would add a new foreign key to the image table - e.g. if there was an unrelated "House" object that needed multiple images, we would add fkHouseId to tblImage. – Rich Gallops Sep 19 '12 at 01:03

2 Answers2

3

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.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

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

Abhinav Sarkar
  • 23,534
  • 11
  • 81
  • 97