JSON colunm vs Reference key approach - Postgres

Question

Which is a good database schema design by considering the performance and long-term large records?

Database: Postgres

I have two ways to design the table structure:

1. Use a JSON column
2. Go with a foreign key reference table?

Option 1: JSON column example,

Table 1: Case (Keep all the Cases)

Table 2: Tags (All the tags will be stored in this table)

Option 2: Have a Reference table

Table 3: Tag_Case_Mapping

Should keep different records for each tags under the same case id.

I need to display tags that are associated with each case in UI, so have to write LINQ join query to display each cases and its respective tags. I can make it in both the way, but I curious about the way which is database design approach in terms of considering the performance.

Answer 1

Before deciding how to store any data, consider how you intend to access it, afterwards.

If you only ever want to look up a Case and then display all the Tags for that case, then "packing" all the Tags into a JSON column will be fine.
However, as soon as you think you might, possibly, want to find all the Cases with a particular Tag, or group of Tags, then properly Normalising your data, with a separate table linking Cases to Tags, is the way to go.

select * 
from cases ; 

+---------+-----------+
| case_id | case_text | 
+---------+-----------+
|      11 | ...       |
+---------+-----------+

select * 
from case_tags ; 

+---------+--------+
| case_id | tag_id | 
+---------+--------+
|      11 |      1 |
|      11 |      3 |
|      11 |      6 |
|      11 |     10 |
+---------+--------+

select c.* 
from case_tags ct 
inner join cases c on ct.case_id = c.case_id
where ct.tag_id ... 

Put a composite Primary Key on both ( case_id and tag_id ) and then index them the other way around, to efficiently support queries going in "either direction".