1

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.

1

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.

| improve this answer | |
  • hmm interesting point. My output will be json. Could i store the load as plain text in mysql? – Citti May 18 '16 at 14:10
  • @Citti I don't get your question. Of course you can store text. But formatted how? – Ziggy Crueltyfree Zeitgeister May 18 '16 at 22:10

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.