Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
2  
Documentation for ORMs (Object Relational Mappers) should have some explanations of various ways they map an object model onto a relational model, and the pros and cons of those options. You also might decide to use such a tool. –  psr May 14 '13 at 21:38
    
You could hold the target as an enum in your code and use the string representation to define the type of the target. You'd only need one class Like which holds this field instead of creating a separate class for every element. Since likes are pretty much the same for every element, there's no need to define separate classes. –  Jeroen Vannevel May 14 '13 at 21:58

4 Answers 4

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.

share|improve this answer
    
This is essentially what we're doing already, It feels wrong because the column count for more complex things like notifications will have something like 2n columns (where n is the number of objects in the system) eg, User created Comment, Event created Comment, User created Comment etc. –  Mike Graf May 15 '13 at 15:08
    
+1 for the description. The correct way to approach this issue is with intersection tables. A few notes about the code that you show: Naming a row Id is a common DB antipattern (a better name is Post_ID, Image_ID, etc.). Also, a pseudokey (eg. in this case Image_ID) needs to be created only if there's no better candidate to be the primary key. –  faif Dec 26 '13 at 19:20

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).
share|improve this answer
1  
Django can do it automatically through concrete model inheritance. The field names would be different, but it looks like your example is otherwise set up identically to how Django does it on the database. (Django also has abstract model inheritance, which would just copy the fields from Likable to its children, so a Foreign Key isn't allowed) –  Izkata May 15 '13 at 0:58
    
Don't the columns user_id, created and updated from Likable belong to the Like table? –  nibra May 16 '13 at 13:38
    
@nibra: no, they belong to every user-generated asset (post, image, etc) on the site. "Likes" are independent; an image uploaded yesterday by user foo may be liked today by user bar. –  9000 May 16 '13 at 15:00
    
Ok, so the user_id in Likable refers to the creator foo of the asset? Then in Like a column user_id is missing to refer to the liking user bar, right? –  nibra May 16 '13 at 17:44
    
@nibra: yes. I should have added table user, too, but I feared it would overload the example which is already pretty large. –  9000 May 16 '13 at 18:21

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>

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.