1

There are two tables - posts and comments:

create table posts
(
    id integer not null primary key auto_increment,
    body text not null
);

create table comments
(
    id integer not null primary key auto_increment,
    body text not null,
    post_id integer not null references posts(id)
);

Now I want to create one more table - reports("bad post" flags) and I want it to store reports for both posts and comments.

create table reports
(
    id integer not null primary key auto_increment,
    obj_type tinyint not null, /* '1' for posts and '2' for comments */
    obj_id integer not null,
    body text not null
);

alter table reports add foreign key(obj_id) references posts(id) on delete cascade;
alter table reports add foreign key(obj_id) references comments(id) on delete cascade;

As you see there are two references in a single field (I differentiate them by obj_id), and the question is - Is it all right to do like this ?

If not what would be better solution?

Thanks in advance.

moriesta
  • 1,170
  • 4
  • 19
  • 38

2 Answers2

1

Intuitively that feels not the right way to do it. I think MySQL would be confused too; how would it validate that a constraint is being met; would it try posts first, or comments first ... maybe both?

Personally I would choose to create two link tables:

  • comments <-> reports
  • posts <-> reports

That way you disambiguate the obj_id properly.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • Actually I use Yii php framework, and I can handle "on delete cascade" from there. I need foreign keys just to use the ORM level relations. And important thing is that posts and comments is just an example, in real I need flags for 4 tables. I want some flexible way without additional 4 tables. – moriesta May 24 '12 at 14:29
  • @Acute to be honest, I have no idea how MySQL keeps those two foreign keys apart =/ perhaps this is a special feature? – Ja͢ck May 24 '12 at 15:08
  • I don't know, but I don't like creating multiple FKs, doesn't look like an elegant solution) Okay, if I decide doing link tables I'll choose your answer as solution – moriesta May 24 '12 at 17:23
  • http://dba.stackexchange.com/q/2754/8975 @DrColossos says it's okay to have many FKs, or did I understand him wrong? – moriesta May 25 '12 at 03:18
  • @Acute he didn't actually mean the constraint that comes with foreign keys, just the idea of a key referencing another table together with a table selector field. See also: http://stackoverflow.com/a/1186979/1338292 – Ja͢ck May 25 '12 at 03:28
0

you just need to reference your comments table since it already references the posts table, this way whenever you get a report you have the key to the comment and the key to the post.

Euclides Mulémbwè
  • 1,677
  • 11
  • 18