I'm looking for advice on how to model the tables for the following entities. The main concept is a Job. There are two Job types, regular ones, and case studies. This question is about case studies.
- A case study job has multiple cases. Each case can have multiple input modifications.
- A case study job has one filter. Each filter can have multiple criteria.
I'm using a PostgreSQL database and currently have the following database structure (normalized)
jobtablecase_table with foreign keyjob_id(the_is to avoid having to quote"case")input_modificationtable with foreign keycase_idfilter_table with foreign keyjob_id(this could be eliminated since it's 1-{0,1} by adding these columns directly tojob)criteriontable with foreign tablefilter_id
The DB sits in front of an ASP.NET web application, and the web client edits the jobs. The client will load an entire Job for editing at once, with all the filter criteria and cases, so I plan to return a JSON document like this (extra columns elided).
{
"caseId": 0,
...,
"filter": {
...,
"criteria": [{
"field": "f",
"value": "v",
}, {
"field": "f2",
"value": "v2",
}],
"cases": [{
"name": "Case 1",
...,
"modifications": [{
"modificationType": 0,
"value1": "foo",
"value2": "bar",
}],
}]
Postgres can generate this JSON easily using select json_array(select json_build_object(/* */) ) with a few subqueries. I'm not sure how to update with this strategy - if I get a JSON back from the client, how could I update it?
I could also use a bunch of LEFT JOINs and loop through the results, creating entities server-side and letting ASP.NET turn that into JSON. That's what I typically do for simple queries or queries with one 1-N join. If I create these entities server-side, then ASP.NET could automatically parse the JSON coming in and it would make the corresponding update statements easier.
That begs the question - should I just store this thing as one big JSON? The web app gets the "entire thing" and sends back the "entire thing" when it's updated - all I'd have to do is store the JSON. This has the drawbacks of data integrity and potentially more difficult schema migration.
One potential drawback is that this limits reuse - perhaps in the future somebody might wish to add a pre-existing case to their job, or reuse a filter in a new job. Keeping these in their own tables would allow that functionality.
I could also create complex datatypes on the server and use array columns to denormalize a bit.
I figured, it's not that difficult of a model, I'd be surprised if this wasn't a "solved" problem. What should I do?