How to structure a database with multiple 1-N relationships?

Question

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)

• job table
• case_ table with foreign key job_id (the _ is to avoid having to quote "case")
• input_modification table with foreign key case_id
• filter_ table with foreign key job_id (this could be eliminated since it's 1-{0,1} by adding these columns directly to job)
• criterion table with foreign table filter_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?

Answer 1

Blockquote should I just store this thing as one big JSON?

My experience is that vanilla columns are easier to sort (index), update and maintain. If you use JSON or JSONB, you may be forced to sort at the client side. And I bet that Postgresql is faster to sort than most client language.

Regarding one to many relation. I use key value for the joining tables to the job table.

1 criteria1
2 criteria2
3 etc

The key is then stored in the job table as job_criteria integer.

https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/212

• Adam: What's the benefit of putting the key in the job table vs. criterion if it's still in its own table? It's just from job j join criterion c on j.criterion_id = c.criterion_id vs. from job j join criterion c on j.job_id = c.job_id?
• sibert: The main benefit is that an integer does not take much space. And you can reuse the "lookup tables" every job. And finally it is easy to maintain. db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/212