Baterson Baterson - 4 months ago 9
SQL Question

Is it good way to create common related table for several tables?

I'm planning db for my some web app. And I find confusing one thing about common table for several tables, for example:

Each row from table

users
,
travels
and
groups
need several images. So I create one table
images
with foreign key to each of these tables and if some new table will need several images, I will create new link from
image
to this table.

Or it is better way to create unique table like
users_images
,
groups_images
and so on, for each table?

Sorry if my question looks too broad, I'm just ORM guy who looking for best practice of planning well db scheme.

Answer

You should use something like

Existing tables

  • User(ID, Name, ...)
  • Travel(ID,Description, ...)
  • Group(ID, Name, ....)

Now you create a table IMAGE(ID,Image_Blob, ...)

And mapping tables like

  • UserImage(UserID,ImageID)
  • TravelImage(TravelID,ImageID)
  • GroupImage(GroupID,ImageID)

All columns with Foreign Key Constraints and index.

The advantages:

  • You can map one image to several entities
  • You can add specific meta information (who linked this and when, comments, ...)

Maybe you get the advise to use a common mapping table with an additional LinkingTo column where you put the source table's name. But doing so, you'd have to trick around with foreign key constraints.

It is an almost general rule: Good databases have many tables :-)