0

Think of a platform-wide commenting-system. A comment can be added to a users profile, a website section, another comment or X other things.

That means that the comment has an "addressee_id" and the addressee_id can reference on different tables.

  • Comments (id, adresseee_id, ..., ...)
  • Sections (id, ...)
  • Users (id, ...)

What is the best way to define on which table to join?

Three possible ways:

  1. Denormalization I remove other tables and add X columns to the "comments"-table Problem: a lot of empty fields.

  2. Another table "pages" with page_ids. Each table gets an extra column "page_id" with FK on pages.id. But here i have to make X JOINS for each request. Sounds expensive.

  3. Comments-table gets an extra column "addresse_type" Here i have to implement IF/ELSE logics into the query which is also expensive and not easy to maintain.

What do you suggest?

Thanks Phil

philippzentner
  • 396
  • 2
  • 11
  • http://stackoverflow.com/questions/21676628/selecting-distinct-notifications-and-prepare-a-clearer-notification-message Check this out. – Neels Mar 24 '14 at 11:02
  • You really want to foreign key on to a single table. You could create an 'objects' table for anything that can be commented on. That table you would have at-least two fields; [object_type_id] *(an 'id' of the table the object is from)* and [object_id] *(the natural primary keys from the root tables)*. Then you have a composite foreign key. To make your joins easier, you could also have a VIEW that UNIONs all of the other tables together; `SELECT 1 AS object_type_id, a, b, c FROM table1 UNION ALL SELECT 2 AS object_type_id, a, b, c FROM table2 UNION ALL ...` – MatBailie Mar 24 '14 at 11:20
  • @Neels Thanks, but i need data from other tables. I can not merge them into one table. – philippzentner Mar 24 '14 at 11:24
  • @MatBailie Perhaps a view is a good solution. Still not sure yet but i really appreciate your input on this. – philippzentner Mar 24 '14 at 11:29
  • I suggest 3 as the method since you have a scenario like this – tarzanbappa Mar 24 '14 at 12:22
  • @MatBailie could you explain more about your approach? Perhaps i don't fully get it. Thanks – philippzentner Mar 24 '14 at 13:44
  • You should use exclusive FKs or inheritance, as described [here](http://stackoverflow.com/a/13317463/533120) (you may also be interested in ["Implementing comments and Likes in database"](http://stackoverflow.com/a/8113064/533120)). You should **not** use option 3, for reasons explained [here](http://stackoverflow.com/a/20873843/533120). – Branko Dimitrijevic Mar 24 '14 at 14:15
  • @BrankoDimitrijevic Great advise! But how does a query look like using exclusive FKs? Is it possible without a function? – philippzentner Mar 24 '14 at 14:38
  • Please clarify: query for what, exactly? Comments of a specific "type" or all comments? The former can be done by an INNER JOIN and latter by a LEFT (or right) OUTER JOIN. – Branko Dimitrijevic Mar 24 '14 at 14:53
  • Nope e.g. i want the single comment with the ID 1 and the details of the type the comment is adressing. The case that the comment is on a user, i want the comment and all of the users details. The query has to determine which FK-column is NOT NULL and has to know which table to join in this case. – philippzentner Mar 24 '14 at 15:01
  • @BrankoDimitrijevic i like your solution as described [here](http://stackoverflow.com/questions/8112831/implementing-comments-and-likes-in-database/8113064#8113064) and i would love to implement the third way of the "ER-Category" but i really don't get all neccessary details on how to do it. I mean.. am i right that i have to make X JOINS for X categories when i just want to have the details for the comment with the id 1? The inheritence model sounds as the cleaner solution. Regarding the exclusive FK-model - think of 10 different categories.. it would be a very big check-constraint and query. – philippzentner Mar 24 '14 at 15:31
  • _"am i right that i have to make X JOINS for X categories"_ - yes for inheritance implemented as "separate types in separate tables" (but see below), not for inheritance implemented as "whole hierarchy in same table" and not for exclusive FKs. – Branko Dimitrijevic Mar 24 '14 at 16:04
  • For the inheritance implemented as "separate types in separate tables", you can use *type discriminator* to avoid the need to "probe" each possible "type" when starting from comments. The ideas is for the "base" table to have the type encoded as the part of its PK (e.g.: `{type, item_no}`), which then gets propagated (through FKs) not only to the "inherited" tables, but to the comments as well. So you have something that looks like option 3, but isn't, since FKs are properly enforced by the DBMS. This complicates generation of PK values through auto-increment, through. – Branko Dimitrijevic Mar 24 '14 at 16:05
  • Also, I forgot to mention that MySQL [doesn't enforce CHECK constraints](http://stackoverflow.com/a/5135068/533120), in case you decide to follow the exclusive FKs route. You'll have to take care of that using triggers or application code, or use a different DBMS. – Branko Dimitrijevic Mar 24 '14 at 16:11
  • Ok thanks. I go with the "separate types in separate tables"-method. This is what i meant with my second-mentioned "way" in the OP. Since i have to use CASE-Syntax to determine which table to join instead of testing all types i have to use stored procedures and i am not able to use normal SQL queries anymore. Am i missing s.th.? Thanks for your support! – philippzentner Mar 24 '14 at 19:34

0 Answers0