So I am developing this request management system web app in ASP.net, C#, SQL server and am currently designing the database. My question is that, I want a request to have multiple comments from different users. I have a Requests table and a Users table. How can I link multiple comments from multiple users to a request?
Should I create a separate table for Comments? then what...
thanks!
You can model a many-to-many relationship with a junction table, e.g. Comments, where each tuple of the relation has a foriegn key to the Requests table primary key, and a foriegn key to the Users table primary key.
I would suggest a separate comments table, with keys of request and user. You will have to implement this in some form, so doing it as an explicit comments table makes sense.
This is a standard many-to-many relationships, and, as devdigital points out, you would normally model this with a junction table. Sometimes this is simply a linking table, but in this case, you can make it a comments table explicitly.
In your coding, you can then create objects of user with a list of comments, and request with a list of comments. And the comments can have a reference to the users and requests, but don't load these in directly, as you will have circular references!
