2

We have an application which accepts JSON objects from X number of third party services and unfortunately we're locked into using SQL to store it. The objects must be flat (no nesting), but each third party can have a different set of properties.

I feel like it would be a better approach to have a static schema - for example, there would be a "Definitions" table that contained an entry for each unique property, then for each object there would be a row for each property that references the definition, for example - given:

{
  "prop_name": "John Doe",
  "prop_tel": "+15555555555"
}

There would be the following in SQL:

Definitions table - 
  | Id | Ref       | ...
  | 2  | prop_name |
  | 3  | prop_tel  |

Entities table -
  | Id | ...
  | 6  |

Values table - 
  | Id | FK_DEF | FK_ENTITY | Value        | ...
  | 9  | 2      | 6         | John Doe     |
  | 10 | 3      | 6         | +15555555555 |

This is slightly simplified from what might be a final design, but in practice this would let you store any format of data in SQL. The expense here is that lookups on this data is hard, you need to use a pivot or nested selects. (and we do need to do lookups on this data, sorting the entities by the values table, etc)

My colleague is convinced that this is ridiculous and we should just be dynamically modifying the schema as we go - either:

  1. Adding a new table for each unique json object we need to store
  2. Adding a new column to a single table for each unique property. Note that properties named the same coming from different third parties or data sources still need to be treated as separate columns.

We can never delete data and hope for this to be a very scalable application - containing hundreds of thousands or potentially millions of records.


Keeping in mind we know both of these options are crap but don't have a choice: Can anyone give me an idea of what the best practices are, and pros/cons of each approach? None of us have had to do this before and we are worried we will get locked into a path and hit road blocks later when trying to scale this. Off the top of my head having absolutely no experience, here's the list I'd make:

Static Schema

Pros:

  • Predictable
  • Maintainable
  • Not difficult to add new data format or perform inserts

Cons:

  • Slower / complicated lookups (pivot or nested select)
  • More complicated inserts / updates (for entities that exist)

Mutating Schema

Pros:

  • Fast lookups
  • Easy updates (for entities that exist)

Cons:

  • Schema out of our control, can get massive and hard to maintain over time
  • Database migrations to new schema gets harder and harder as time passes
  • Its not recommended for production app to have access to change schema

What am I missing and what would be the best path to take considering long-term scalability and maintenance?

closed as primarily opinion-based by Marco, Colin 't Hart, mustaccio, McNets, dezso Apr 26 '17 at 15:03

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    The generic SQL solution goes under the name Entity Attribute Value (EAV). There are a great many posts out there explaining how horrid it is, and a few showing how it can be made to work. It will not scale, however, to next-big-thing proportions, but can be useful in niches or to get by while you think of something better. – Michael Green Apr 25 '17 at 10:56
  • 1
    I don't understand "Database migrations to new schema gets harder and harder as time passes" why would they get harder? Also did you consider just storing the JSON objects "as is" in a JSON column? Modern DBMS have pretty good support for querying JSON documents – a_horse_with_no_name Apr 25 '17 at 11:38
  • @a_horse_with_no_name: I didn't know about JSON support in SQL, looking into this now. As for the schema, typically you would track every change to the schema and be able to migrate forward or backward to any revision, but if you are dynamically adding columns or tables extra considerations will need to be made and/or handled separately. – caesay Apr 26 '17 at 3:05
1

If the data is going to have many different layouts, you might be overthinking it.

Many DBMSs have great support for JSON now. You can store it natively as JSON while defining a view on top of JSON data that exposes it to other users as a regular view with columns.

https://www.postgresql.org/docs/current/static/datatype-json.html

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

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