JoeyH3 Posted June 6, 2012 Share Posted June 6, 2012 Hello all! I'm beginning to design a website which will be primarily focused on allowing users to upload and share drawings/artwork that they created. I also want to allow users to create profile pages on this site, and be able to post blog entries. Additionally, I want to allow users to be able to leave comments on uploaded images, user profiles, and blog entries. However, I'm having difficulty trying to envision the most efficient way to design the database in order to allow this to happen. At first, I thought of simply having three comment tables - one for image comments, one for profile comments, one for blog comments. However, that just seems inefficient maintenance wise, as if I want to make any changes to the commenting system, then I have to update and test three tables instead of one. Next, I thought of having a single comments table, and having two columns for referencing their parent object - one would be the PK of the blog/image/profile, and the second column would be the type. So then the query for selecting a comment for a journal would look like: select (columns) from comments where reference=(PK of journal) and type="blog". So far, that's been the best idea I could come up with, as that would (likely) guaranty that a comment meant for an image wouldn't accidentally get displayed on a blog entry that ended up having the same PK as the image (I plan on using auto_increment integers on most of the primary keys). However, I just wanted to ask if that is a good way to approach the design, if there were any potential flaws I could be overlooking, or if there is another way that would be better practice? Thanks! Joey Quote Link to comment https://forums.phpfreaks.com/topic/263742-database-design-comments-in-multiple-locations/ Share on other sites More sharing options...
Jessica Posted June 6, 2012 Share Posted June 6, 2012 Sounds good to me. Quote Link to comment https://forums.phpfreaks.com/topic/263742-database-design-comments-in-multiple-locations/#findComment-1351653 Share on other sites More sharing options...
ignace Posted June 6, 2012 Share Posted June 6, 2012 Alternative options are: A) model the relation and take advantage of the relational model: comments (comment_id) blog_comments (blog_entry_id, comment_id) artwork_comments (artwork_id, comment_id) profile_comments (profile_id, comment_id) because the type column has a few drawbacks which you may not have considered, it's difficult for example to left join using a value in a type column. B) create a comments table for each type instead of just having one comments table. Quote Link to comment https://forums.phpfreaks.com/topic/263742-database-design-comments-in-multiple-locations/#findComment-1351687 Share on other sites More sharing options...
JoeyH3 Posted June 7, 2012 Author Share Posted June 7, 2012 I like Idea A - it seems simply, easy to implement, and yet still organized. Thank you, your advice is much appreciated Quote Link to comment https://forums.phpfreaks.com/topic/263742-database-design-comments-in-multiple-locations/#findComment-1351785 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.