Database Recommendations

Question

I have a table in which comments are stored, the comment can come from another user or another profile, which are separate objects in this application.

My initial thinking was that the table would have both user_id and profile_id fields, so if the user submits a comment, he gives user_id leaves blank_file_id

This is right, wrong, is there a better way?

Answer 1

In the past, I used a centralized comment table and had a fk_table link field.

eg:

comments(id,fk_id,fk_table,comment_text) 

Thus, you can use UNION queries to concatenate data from multiple sources.

 SELECT c.comment_text FROM comment c JOIN user u ON u.id=c.fk_id WHERE c.fk_table="user" UNION ALL SELECT c.comment_text FROM comment c JOIN profile p ON p.id=c.fk_id WHERE c.fk_table="profile" 

This ensures that you can expand the number of objects with comments without creating redundant tables.

Answer 2

Whatever the best solution, IMHO depends not only on the table, but also on how it is used elsewhere in the application.

Assuming the comments are related to some other object, let's say you retrieve all the comments from that object. In the proposed design, to extract all comments, you need to select only one table, which is effective. But this is extracting comments without extracting poster information for each comment. Perhaps you do not want to show this, or maybe they are already cached in memory.

But what if you needed to get information about the poster when you received comments? Then you need to join two different tables, and now the resulting record set is polluted with a lot of NULL values ​​(for the profile comment, all user fields will be NULL). The code that should analyze this result set can also become more complex.

Personally, I probably start with a fully normalized version, and then denormalize when I start to see performance issues

There is also a completely different possible solution to the problem, but it depends on whether it makes sense in the domain. What if the application has other places where the user and the poster can be used interchangeably? What if the user is just a special kind of profile? Then I think that the solution should be decided mainly in the user / profile tables. For example (some abbreviated pseudo-sql):

 create table AbstractProfile (ID primary key, type ) -- type can be 'user' or 'profile' create table User(ProfileID primary key references AbstractProfile , ...) create table Profile(ProfileID primary key references AbstractProfile , ...) 

Then any place in your application where a user or profile can be used interchangeably, you can refer to LoginID.

Answer 3

If comments are shared by several objects, you can create a table for each object:

 user_comments (user_id, comment_id) profile_comments (profile_id, comment_id) 

Then you do not need to have empty columns in the comment table. It will also make it easier to add new comment source objects in the future without touching the comment table.

Answer 4

Another solution is to always denormalize (copy) the commentator’s name of the comment, and also store the link back to the commentator through the type field and id. Thus, you have a table of unified comments in which you can quickly search, sort, and crop. The disadvantage is that there is no real FK relationship between the comment and its owner.

Answer 5

Here is another approach that allows you to maintain referential integrity through foreign keys, manage it centrally and ensure maximum performance using standard database tools, such as indexes and, if you really need, partitioning, etc.:

 create table actor_master_table( type char(1) not null, /* eg 'u' or 'p' for user / profile */ id varchar(20) not null, /* eg 'someuser' or 'someprofile' */ primary key(type, id) ); create table user( type char(1) not null, id varchar(20) not null, ... check (id = 'u'), foreign key (type, id) references actor_master_table(type, id) ); create table profile( type char(1) not null, id varchar(20) not null, ... check (id = 'p'), foreign key (type, id) references actor_master_table(type, id) ); create table comment( creator_type char(1) not null, creator_id varchar(20) not null, comment text not null, foreign key(creator_type, creator_id) references actor_master_table(type, id) );