MySQL列json vs连接

Question

I am stuck with database design of holiday package inventory project which contains a main table packages for storing package information.

Here is the fields

1. id PK
2. package_name varchar
3. attractions json eg-field: ['super','funny'],
4. inclusions json eg-field: ['hello','cool']

One package may have many attractions and inclusions so that's why i choose a json field.

So is this a slandered way or keep the attractions and inclusions in another table with foreign key relation??.

if i choose second method(ie different tables for attractions and inclusions) what about searching a package with particular attraction think that search requires a join query (Search with join is a bad practice??.).

But in the first method we can apply a json search (MySQL-version >5.7 supports json search).

Answer 1

It depends on how complex you foresee the project becoming. You can use your existing table configuration and leverage the JSON Search feature, however as your table gets bigger this will be a bigger and bigger problem for performance. It would be better to create 4 tables, Packages, Inclusions, Attractions and Relations.

Packages

• ID (int) PK
• Name (varchar)

Attractions

• ID (int) PK
• Name (varchar)

Inclusions

• ID (int) PK
• Name (varchar)

Relations

• ID (int) PK
• PID (int) FK - Packages
• RID (int) ID of Inclusion or Attraction
• type (varchar) indicates Inclusion or Attraction

Then you can do joins to select any combination of the types and relate any number of them to eachother.

SELECT * FROM Packages p join Relation r ON p.ID = r.PID AND r.type = "Attraction" Left Join Attractions a ON a.ID = r.RID WHERE p.id = 1 /*specific package id*/