Struggling with how to set this up for speed and scalability.
So basically I have a bunch of posts that depending on what type of post it is, it could have different input values that I would need to store. So what I am thinking of is storing the common values together in a "posts" table and the storing the Type specific values all in separate Type specific tables... Is that scalable? especially when I want to pull say ALL posts?
what I am thinking below:
posts: id, type_id, commonValue1(varChar), commonValue2(int), commonValue3(boolean), commonValue4(boolean) Type1: id, post_id, Type1SpecValue1(varChar), Type1SpecValue2(int), Type1SpecValue3(boolean) Type2: id, post_id, Type2SpecValue1(int), Type2SpecValue2(varChar) Type3: id, post_id, Type3SpecValue1(varChar), Type3SpecValue2(int), Type3SpecValue3(int), Type3SpecValue4(boolean), Type3SpecValue5(varChar)
This will obviously expand as time goes on and I will add more types ... any ideas? The other thought was I just create different Type tables with all the values in them (even the common values) and then just have a posts table for when I need to pull multiple types? thoughts on how to set this up?
Just to clarify those specific values could be
varchar etc. or else I would just do a many to many with items table but I don't think that will work with different field types.