Mysql – database setup for posts table with multiple types

MySQLtable

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:

Tables: 

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?

—Update—

Just to clarify those specific values could be int, 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.

Best Answer

You may want to consider using a single JSON field on your posts table to store all the miscellaneous details in a JSON object. You can have as many as you want, including arrays and nested objects.

At this time MySQL is not great at indexing and accessing/manipulating JSON data, but it does provide a few functions like JSON_OBJECT, JSON_ARRAY, JSON_SET, JSON_INSERT, JSON_REMOVE, JSON_REPLACE, JSON_MERGE, and JSON_EXTRACT, and surely future version will have even greater support, such as indexing.

MariaDB has also Dynamic Colums and also good (possibly better) support for JSON data than MySQL.

Related Question