I've got a table which stores comments, the comment may either originate from another user, or any other profile that are separate organizations within this application.
My original thinking could be that the table might have both user_id and profile_id fields, therefore if a person submits a comment, it provides the user_id leaves the profile_id blank
is right, wrong, it is possible to better way?
When the surveys are general for many objects you can produce a table for every object:
user_comments (user_id, comment_id) profile_comments (profile_id, comment_id)
Then you don't have to possess any empty posts inside your comments table. It will likewise allow it to be simple to add new comment-source-objects later on without touching your comments ought to table.
A different way to solve would be to always denormalize (copy) the title from the commenter around the comment as well as store a reference to the commenter using a type as well as an id area. This way you've got a unified comments table where on searching, sort and trim rapidly. The drawback is the fact that there is not any real FK relationship from a comment and it is owner.
Previously I have tried personally a centralized comments table coupled with a area for that fk_table it's referencing.
That method for you to use UNION queries to concatenate the information from the 3 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 guarantees that you could expand the amount of objects which have comments without creating redundant tables.
Whatever is the greatest solution is dependent IMHO on not only the table, but additionally how this really is used elsewhere within the application.
Presuming the surveys are all connected with a few other object, allows say you extract all of the comments from that object. Inside your suggested design, removing all of the comments require choosing from only one table, that is efficient. But that's removing your comments ought to without removing the data concerning the poster of every comment. Maybe you won't want to show it, or possibly they're already cached in memory.
But let's say you needed to retrieve details about the poster while locating your comments ought to? Then you've to become listed on with two different tables, and today the resulting record set gets polluted with many different NULL values (for any profile comment, all of the user fields is going to be NULL). The code that needs to parse this result set also might get more complicated.
Personally, I'd most likely begin with the fully stabilized version, after which denormalize after i start to see performance problems
There's additionally a different possible means to fix the issue, but this is dependent on whether it seems sensible within the domain. Let's say you will find other areas within the application in which a user along with a poster may be used interchangeably? Let's say a person is simply a special type of an account? I Quickly believe that the answer ought to be solved generally within the user/profile tables. For instance (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 around the application, in which a user or perhaps a profile may be used interchangeably, you are able to reference the LoginID.