0

I have this issue. I have three types of posts a user can generate (some users can generate all some can only generate one type). I have the following types: main, employee and admin posts.

I expect to have to deal with millions of posts (all three types). But about 90% of all posts would be main_posts. Also, most reads from the DB would be of the main_posts too.

NOTE
The main_posts table would have about 50-100 more fields than what is currently there. update those fields would be normalized into different tables. The ideea is that the main-posts table would have the bulk of the posts and therefore would have the most reads and writes.

Which design option is more sound?

Option1
Option 1

or Option 2
Option 2

Thank you.

Improve this question
10
  • I think that you could (possibly - don't know the full scenario) greatly simplify your design with just one post table with a post_category field with values 'emp', 'admin' or 'main'. Have you considered this option?
    – Vérace
    Nov 19, 2015 at 23:41
  • Well, I forgot to add that the main_posts table would have another 50-100 extra fields. The simplified design would not work!
    – Cristian
    Nov 19, 2015 at 23:44
  • 1
    Hang those extra fields off the main post table? Just throwing ideas out there!
    – Vérace
    Nov 19, 2015 at 23:52
  • @Vérace I will add them but was wondering which option would be better.
    – Cristian
    Nov 19, 2015 at 23:53
  • The problem is that both designs could probably be made to work with accompanying code - it's very difficult to give definitive advice in such situations. I know from experience that you can start with a design and as you're coding, you're modifying the design constantly. Basically, if I were you, I'd prototype both and gradually evolve a design rather than try and definitively start with something and rigidly stick to it. I'm sorry that I cannot be more helpful, but some questions are impossible to answer with a simple "yes, you should pick no. 2".
    – Vérace
    Nov 20, 2015 at 0:10

2 Answers 2

Reset to default
1

Read this post how StackExchange does it:

http://www.brentozar.com/archive/2014/01/how-to-query-the-stackexchange-databases/

Improve this answer
1

Coding your application should not drive your database design. You should first write down the biz rules of your app. It seems you have different type of users (employee, admin and regular users), which mean they'll have different needs. Option2 seem a good approach. User table should have userType. REgarding the images, do they belong to the user (they post them) or it's in the application? If image belongs to user, then you should have User_image table. If image can be shared between different user_type then your design in option 2 is correct. Make sure to write down all the business rules, it will help you design your database and speed up your coding development (less db modification).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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