How to genericize foreign key references in table?

I am trying to design a table to store references to multiple tables as a “target” of the item in each row.

An example should clarify what I am trying to do:
Imagine you have 4 tables (images, videos, comments, likes)

with likes being similar to

CREATE TABLE `likes`
`target` int(11),
`user_id` int(11),
`created_at` timestamp

How do you make it so the “target” could be a row of images, videos or comments (eg, you like an image, video or comment)

Also how would you go about representing this within your code? (abstract class Like and concrete classes for LikeImage, LikeImage, LikeComment?) or maybe have an interface ILikeable and then the Like object has a ILikeable object on hand?

I’m running into this pattern more and more often making things such “like” (like an object) or “subscribing” (subscribe to changes on an object) to various targets and generating “notifications” (notify a user of a change on an object) that are generically able to target objects.

Thanks for sharing your knowledge.

Update:: Is there a nosql way to easily achieve this?

TLDR;

In my experience if you are going to have relationships between tables, they should be proper foreign keys, enforced with constraints. If you go for a weak reference such as the one you describe you’ll eventually run into trouble.

You can do this by using inheritance in your object model, and represent it in your database with 1-to-1 foreign keys from the ‘superclass’ table to the ‘subclass’ tables, and a foreign key from your ‘likes’ table to the ‘superclass’ table. Read on for a more full explanation.

The full story
The way that Entity Framework does this would be to use inheritance in your object model (ie Post, and subclasses Image, Comment, Video). In the databases there would then be a table called Post, with all the fields common to all types, and tables Image, Comment, Video for the fields specific to each type. The primary key of each subclass tables is also foreign keyed of the primary key of Post (so there is a 1-to-1 mapping between Post.Id and Image.Id etc, it they are the same id). Your likes table then foreign keys to Post.

Psuedo-SQL below:

table Post (
    Id int not null PK,
    UserId int not null REFERENCES User.Id
)

table Image (
    Id int not null PK REFERENCES Post.Id,
    ImageUrl varchar(250) null
)

table Likes (
    Id int not null PK,
    UserId int not null REFERENCES User.Id,
    PostId int not null REFERENCES Post.Id
)

I have also come across an example where there is a table called something like ‘LikeableType’ which has an Id, and the name of the table that type in question references (so you would have a row for each of Image, Comment and Video). Your ‘Likes’ table then has a ‘LikeableType’ field, and a ‘LikeableId’ field. Your lookup process would then be something like:

LiekableType = 1 therefore table = ‘Image’
Select from table where Id = LikeableId

The problem here is twofold: firstly, you can’t enforce this with a foreign key constraint, because it is a meta-relationship, rather than a real relationship at the database level. Secondly – and this occurred in the example I have seen – is that the key types might be different for each table you want to reference (ie Image has an int key, but Video has a varchar key), and so casting to varchar etc is necessary so that you can deal with keys in a common format.

A genericized approach I once used:

table Likable (
  id int not null primary key, -- the common ID
  user_id int not null, -- the creator of the likable asset
  created datetime,
  updated datetime,
  ... -- etc
);

table Post (
  id int not null primary key,
  title varchar,
  text varchar,
  ... -- etc
  foreign key (id) references Likable(id)
);

-- a Picture can only be attached to a Post
table Picture (
  id int not null primary key,
  title varchar,
  url varchar,
  width int,
  height int,
  post_id int not null,
  ... -- etc
  foreign key (id) references Likable(id),
  foreign key (post_id) references Post(id),
);

-- a Like can be added to anything Likable
table "Like" (
  id int not null primary key,
  target_id int not null, -- the asset 
  user_id int not null, -- the user that liked it
  foreign key (target_id) references Likable(id)
);

This allows you to make sure anything you attach a “like” to is a Picture, or a Post, but not anything else.

I think it is possible to explain this setup to SQLAlchemy; not sure about Django ORM.

Downsides:

you have to insert things twice and make sure you don’t accidentally create a Likable without a linked or or Picture.
You have to travel to Likable for common fields; it can cost you an extra request if you’re not careful.

Upsides:

Referential integrity across the board 🙂
Common ID space for likes, and it is easy to add more kinds (Video, Music, etc.).
Easy and consistent handling of common fields in Likable (in my case I had a lot of them).

I see three different solutions from a DB design point of view. I am not sure how your ORM layer (entity framework) will look based on these designs, but I like to approach these things from a DB design first.

Option One

The one that you mentioned – but to enforce referential integrity you’d need to add another column:

CREATE TABLE 'likes' (
'target' int(11),
'targetTypeID' int(11),
'user_id' int(11),
'created_at' timestamp
)

So you’d then need a targetType table, which has 3 entries in it; Video, Image and Comment. Based on this table you can then write a trigger to enforce referential integrity – you would check the targetTypeID value and then check the corresponding target value. . A bit messy, but it’d work. If you wanted to make this easier you could add TargetTypeID as a column onto your Video, Image and Comment table, and then you can query it like so:

Select  l.*
from    Likes l
left outer join image i on l.target = i.imageid and l.targettypeid = i.targettypeid
left outer join video v on l.target = v.videoid and l.targettypeid = i.targettypeid
left outer join comment c on l.target = c.commentid and l.targettypeid = i.targettypeid

which will help you avoid accidently joining a “Video Like” row in the Like table to the wrong table, eg like a “Image” row. And yes if this is possible, it will definitely happen.

Option Two

Create your table like so:

CREATE TABLE 'likes' (
LikeID int primary key,
ImageID int null,
VideoID int null,
CommentID int null,
'user_id' int(11),
'created_at' timestamp
)

You can then put in a constraint (or trigger) that says either “ImageID” or “VideoID” or “CommentID” must be populated. The main benefit of this design is that you can fully enforce referential integrity using the DB. Downside of course is that you’re “wasting” two columns which will always be null. The other downside is that your queries will look like this:

Select  l.*
from    Likes l
left outer join image i on l.imageid = i.imageid
left outer join video v on l.videoid = v.videoid
left outer join comment c on l.commentid = c.commentid

which is somewhat annoying. But maybe you won’t need to do that kind of thing very often, especially if you put the common stuff (Eg, name, description) into the likes table.

Option Three

Using “inheritence” or a base table. Create a new table named “ItemLike”:

create table ItemLike (
    ItemLikeID int,
    TargetTypeID int,
    ImageID int null,
    VideoID int null,
    CommentID int null,
)

and change your like table to be like this:

CREATE TABLE 'likes' (
    LikeID int primary key,
    ItemLikeID int foreign key,
    'user_id' int(11),
    'created_at' timestamp
)

very similar to the second answer but just adding another level of abstraction and might make your entity framework classes nicerer. Kind of pointless from a DB design perspective unless you want to make ItemLikeID nullable (can’t see why you would) – but it might make your table partitioning easier if you get lots and lots of data in your like table. Your joins then look like this:

Select  l.*
from    Likes l
inner join ItemLike il on l.ItemLikeID = il.ItemLikeID
left outer join image i on il.imageid = i.imageid
left outer join video v on il.videoid = v.videoid
left outer join comment c on il.commentid = c.commentid

Or you could use a NoSQL database and all of your problems will go away! </sarcasm>

This sounds to me like a SuperType – SubType relationship should be used. The SuperType is a single table that stores all of the common attributes of all the SubTypes along with key that indicates what kind of SubType it is. For example:

tblLikes (SuperType)
LikedId, (PKey)
LikedType, (1=Images, 2=Videos 3=Comments)
LikeShortDescription,
UserId,
DateStamp

tblLikedImages (SubType)
ImageId, (PKey)
LikedId, (FKey tblLikes)
…
ImageData Goes here
…

tblLikedVideo (SubType)
VideoId, (PKey)
LikedId, (FKey tblLikes)
…
VideoData Goes here
…

tblLikedComments (SubType)
CommentId, (PKey)
LikedId, (FKey tblLikes)
…
CommentData Goes here
…

The concept allows for scalability. Each SubType may or may not belong to the SuperType. A query of the SuperType table can give a high level view of all the “Liked Items”. Unfortunately if you need the details of each SubType… separate querries need to be executed.

Filed under: softwareengineering - @ 08:38

Thẻ: database-design, design-patterns, sql

Thiết kế website giá rẻ

Danh mục

How to genericize foreign key references in table?