1

I have the following problem that I want to solve. I want to add a column of IDs, which should connect one comment to one of the tables above, e.g. Videos. I don't want to add a CommentID column to the tables above, since each of the tables could have multiple comments.

enter image description here

Idea 1: I know that the database, can also be designed as the following with joins in the query. The query would look something like that: SELECT * FROM Comments c JOIN Videos_Comments vc ON c.CommentID=vc.CommentID JOIN Videos v ON vc.VideoID=v.VideoID ...

enter image description here

Idea 2: Another idea, would be to prefix the IDs of each table e.g. V12.

Edit: Idea 3 (by yair):enter image description here

Is there another more approved way of implementing it?

Tl;dr: How can I accomplish having IDs of different tables in the same column and at same time beeing able to preserve the origin of the ID.

Code Pure
  • 41
  • 5
  • Possible duplicate of [MySQL - Conditional Foreign Key Constraints](http://stackoverflow.com/questions/2002985/mysql-conditional-foreign-key-constraints) – jperelli Mar 19 '17 at 00:52
  • Also duplicate of [Possible to do a MySQL foreign key to one of two possible tables?](http://stackoverflow.com/questions/441001/) – Bill Karwin Mar 19 '17 at 01:23
  • @jperelli I don't believe anywhere in his question is there anything about creating a foreign key constraint. Rather he is just asking the best setup to have one table that is referenced by many tables. – Edward Mar 19 '17 at 18:24
  • @CodePure did you ever come up with a working solution? Did either of the posted answers help you in getting the results you wanted? It's good decorum to select an answer or provide updates and not just leave a question hanging. – Edward Mar 28 '17 at 18:28

2 Answers2

2

If I understand your question correctly, you can add a VideoID column straight into Comments.

However, if you have comments also for things that aren't videos (like Posts or Pictures tables), than forget about the VideoID column - it's bad. Instead, add two other columns: CommentedOn and CommentedOnID.

CommentedOn represents the type of thing on which the comment was commented. Possible values are videos, posts or pictures (in case these are the type of things for which you keep comments).

CommentedOnID is self-explanatory (but I'll explain). It's the ID of corresponding row in Videos (or Posts or Pictures) on which this comment was commented.

Please note that there might be better names than the names I used (CommentedOn and CommentedOnID). I used them only because I don't know enough about the tables to which the comments refer. For instance, if the comments were on videos, pictures and audios - I'd name the columns MediaType and MediaID. Names are important.

For instance, if you have a video of which ID is 123454321, in order to retrieve all comments for that specific video, you could use a simple query with no JOINs:

select * from Comments where CommentedOn = 'videos' and CommentOnID = 123454321;

yair
  • 8,945
  • 4
  • 31
  • 50
  • Good answer. Maybe show a query for the questioner on how you might know which table is being queried dynamically based on CommentedOn column – Edward Mar 19 '17 at 01:26
  • Thank you very much for yout answer. I edited the question, to include your suggestion, but I'm still strugelling with the query. – Code Pure Mar 19 '17 at 02:27
  • @CodePure I added an example query that shows how to retrieve all comments for a specific video. HTH – yair Mar 19 '17 at 10:25
  • @yair Actually I was thinking a more dynamic query. So I posted an answer that will allow for such. – Edward Mar 19 '17 at 18:43
  • @Edward why using a dynamic query when a clean and simple query suffices? – yair Mar 19 '17 at 22:19
  • Well true, for fun, even if a new media type table was added you could still do it the simple way. – Edward Mar 19 '17 at 22:44
0

As you can see there are many ways to implement the tables. I have provided a way to make your query dynamically in by using a cursor setup. Also I provided another way by creating a procedure that you can then pass in the tableName you want to work with and have it return all comments based on that table name. These of course are based in part by idea 3 without the extra CommentsOn table but with the CommentTable having a column CommentOn (nvarchar(64)) and not CommentOnId, and of course the MediaId is the condition that is equal to the primary key of the table within the CommentOn field. I also suggest the differing mediaTables have the primary key name just set to Id, not {tableName}Id, but if you must keep it like that you can just change some of the code below to make it work but this is a lot more complicated having the table names pluralized.

The dynamic query:

declare @tableName AS Table(Id nvarchar(64))
Insert into @tableName 
    Select distinct(CommentOn) from Comments
declare @tempId nvarchar(64)
declare cur cursor for 
    select Id from @tableName 
open cur
fetch next from cur into @tempId
while @@FETCH_STATUS =0
    Begin
        declare @sql as nvarchar(max)
        set @sql = 'Select t.*,c.* from ' + @tempId + ' as t join Comments as c on t.Id = c.MediaId where c.Comments = ''' + @tempId + ''''
        exec(@sql)
        Fetch Next from cur into @tempId
    End
close cur

The procedure way:

create procedure GetComments 
    @tableName nvarchar(64)
AS   
    SET NOCOUNT ON;  
    declare @sql as nvarchar(max)
    set @sql = 'Select t.*,c.* from ' + @tempId + ' as t join Comments as c on t.Id = c.MediaId where c.Comments = ''' + @tempId + ''''
    exec(@sql);

Which is then called like this:

exec GetComments N'Videos'

You may want to change the columns that are returned from "t.*,c.*"

Edward
  • 864
  • 1
  • 7
  • 29