I'm creating a new site which will have many objects, such as posts(text/photo/video), products, photo galleries, etc.

Currently my design is to put all of the objects into a posts table, but I'm starting to wonder if that might be too restrictive.

I also have a post_tag pivot table...

Would I be better off creating a posts table, and a products table, and a videos table, etc? I know the posts table could get large, but I think it would also be easier to maintain. OTOH I could break each object table into a separate database if the need ever arose, which (I think?) would help with scalability, which would also mean I'd probably need more pivot tables: tag_video, product_tag, etc. for each object table?

I'm curious: how does Tumblr do this for each of their post types...?

Any thoughts would be greatly appreciated!

  • You should think more about the sharing aspect. Can a photo/video be shared by multiple posts, without sharing the full post that originally included the photo/video? If yes, then it must be a separate table. – Andreas Nov 5 '15 at 1:24
  • @Andreas, no, each post is an individual object. If somebody wanted to upload a photo as a post and as photo in a gallery, they'd have to upload that photo twice. – timgavin Nov 5 '15 at 2:12
  • @timgavin, I question that design. If you have a gallery of images, it would make sense to be able to link to one of these images without uploading it again in the post. – user82096 Nov 5 '15 at 6:10
  • @dan1111 well, the difference is that the galleries are protected - for members only - and the single posts aren't, so the content wouldn't be shared anyway. However, your comment has started me thinking about other possibilities. :) – timgavin Nov 5 '15 at 14:42
  • @Andreas I originally wasn't going to have sharing, but I think I will now. Thanks for your input. – timgavin Nov 5 '15 at 18:28

Ultimately this depends on: What do you want to do with the content?

A single table of posts works fine as long as you only want to access the content as posts. However, it will become quite limiting if you want to use the content in different ways. What if you want to find all of the images that are embedded within posts? This would be very difficult if you only have a posts table, since you would have to process each post to find the images.

In my opinion, separating the different types of content into different tables is a good idea, because with a complex site like you describe, you are almost certain to want functionality that will process the content in different ways. This may not be clear now, but these uses cases are likely to arise later.

Keep in mind that a large number of tables is only one kind of complexity. Writing a lot of extra code to work around an overly simplistic database design adds complexity of its own. And actually, having a lot of a tables is not that big of a deal to manage (though it may seem so at first if you aren't experienced at database design).

  • Your comments make sense, and are what I've been looking for. I'm always concerned about scalability, and you've pointed out some narrow thinking on my part ("This may not be clear now, but these uses cases are likely to arise later."), which I appreciate. So I'm going with separate tables. – timgavin Nov 5 '15 at 14:48
  • I'm actually thinking about breaking each content table into two parts, such as: posts and posts_content, so that the posts table has the PK, created date, etc, and the posts_content table has the content (text/photo/video), so that each post won't be limited and can be scaled as well. A post can have one photo, or a hundred photos, and can be updated and expanded (or contracted) at will. – timgavin Nov 5 '15 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.