2

I am pretty new to designing databases, and currently, I am working on a substantial big project of mine which requires a pretty big database. Here for I have a couple of questions to get my database ready for implementation. --Do have in mind that this project is focused on Laravel--

Question 1: My project makes use of posts, But not only one. I have a system where three sorts of posts can be created, a standard post, a profile post and a Company post. All these posts can contain images. Currently, I have a column inside of all these different post tables called Post_photo'. Is this the right way to store pictures that associate with a post? It is illustrated in the image below,

Image: https://i.stack.imgur.com/F4hp7.jpg

Question 2: Every post can contain comments, And to connect these comments to a post you need to refer them one. But because I have three different variations of posts I set my comments table up like this; "Comment table consists of a Post_ID column and a Company_post_ID column" Instead of it having one Post_ID. Is this the right way to connect comments to posts? Or do I need to make another table called company_comments? If not, How can I accomplish this? I have this same system on my likes and category table as well because I need to refer my likes and categories to posts. Is this the right way? To get a visual of what I am talking about, There is a picture above.

Thanks for taking the time to read this!

Rainier laan
  • 1,101
  • 5
  • 26
  • 63

1 Answers1

1

The following assumes that you are using a relational database.

Answer 1: If there can be more than one picture or file per post, then the best practice would be creating a table for photos that references the post's ID.

This way when you load the post you would query the photos table for columns containing a PostID field matching your post's id.

Answer 2: If the three types of post are very similar (and contain similar data), consider having only one post table, and include a field that indicates the type of post. For example, a field called postType could store an integer (0-2) that corresponds to the type. This would simplify your comments table, as you would only reference the postID.

As a final note, you might find this thread about storing binary data in databases helpful: (Storing files in SQL Server)

Matthew Strauss
  • 51
  • 5
  • First of all, I want to thank you for answering, and You're right about the first question. Thanks for that! But back to question 2 In my case, The three types of posts all have different columns as you can see in the picture that I provided in the question, They all need different information. So with that in mind, I assume that part of my database is correct? If that is the case, I can't use the field PostType, Because they all require different information. How do I manage the comments then? Do I have to make separate comment tables for each post table? (1/2) – Rainier laan Nov 02 '17 at 19:51
  • Furthermore, each post can be liked. Imagining the situation above, Is my likes table set up the right way? Can I use ONE like table for all my different versions of posts? Or do I need to make separate tables as well? (2/2) – Rainier laan Nov 02 '17 at 19:51
  • Sorry about the misunderstanding, Imgur was down earlier. Because you have three kinds of posts stored in different tables, but they use comments in the same way, you can use one comments table with only one postID field, if you also include a postType field. This solves the problem of non-unique postID's by combining them with the type. So if you pull up a Company post your query would look for all comments that have the correct postID AND postType = 'Company'. This should work for categories, but it wont work for likes if you want them to be limited to one per user. – Matthew Strauss Nov 02 '17 at 22:07
  • Okay, So I think I can go further with this, thanks! But about the likes, You are right about "Likes need to be limited to one per user". But only a standard post and a profile post can contain likes. How can I accomplish this in my database? Because eventually, I want to make a notification system where the user can see who liked their post and on what time they did that. Is this the right way to handle likes? I looked for a solution from someone that is trying to do the same sort of thing, but I can't find any. Do you have any suggestions? – Rainier laan Nov 03 '17 at 13:41
  • I have some ideas, but efficiency might be a concern. My best guess would be: create a table called PostUserLike and add a field to the various post tables called likes. The PostUserLike table would contain 3 fields: UserID, PostID, and PostType. When a user likes a post, increment the likes field in the post table, and add an entry to the PostUserLike table with the user's id, the post's id, and the post's type. – Matthew Strauss Nov 03 '17 at 14:43
  • When any given user looks at a post you would look at the post table to get the total number of likes, and you would look at the PostUserLike table to see if that user has already liked that post to prevent them from doing it again. This would also allow users to see a list of all posts they have liked if you want. – Matthew Strauss Nov 03 '17 at 14:43
  • Okay, Thanks for this extensive explanation. This is what I have now, * Image: https://imgur.com/a/Z4ScK* But I couldn't follow up on for instance the like part because I can't understand what you meant. Besides, My native language is not English. I added the postType and PostUserLike tables, but I don't know if I did it right. I did not change my Likes tables though. I do not like to ask but do you maybe want to make a visual presentation of your solution like the image above? That will make me understand it better. Thanks for taking the time to help me. – Rainier laan Nov 03 '17 at 15:52