0

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.

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by smantscheff
0

Your attempt is definitively flawed. If you implement it like that, a deletion of a post may lead to the deletion of reports on comments with the same id.

Another solution might be to combine posts and comments in one table, since they only differ in the reference to a parent. With this structure

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

a record without a post_id is a post, and with a post_id it is a comment. Then you need only one reports table which refers to the unique id of posts_and_comments.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.