Thank you for your invitation. Please note that the table of your requirements is not too relevant. It will lead to high maintenance cost in the later stage.
Food and Beverage tables, Transportation tables and Hotel tables should be independent public tables.
the json or serialization is stored in the master table to store the catering, transportation and hotel information selected by the user. Note: here, the order price should be stored in the field of the main table along with the order price at that time. The hotel price of tourism will change from time to time and should not be queried by binding. When the user places an order, all prices should be fixed on the amount at the time of placing the order. According to the above conditions, you should establish the following data sheet

Public table

• Food and Beverage list (for example, Beijing Haidilao, Shanghai Haidilao, specific table fields are associated with city table)
• Traffic table (including Beijing traffic, Shanghai traffic, etc., specific table fields are associated with city table)
• City Table
• Hotel table (for example, Beijing Home, Shanghai Home, specific table fields are associated with city table)
• scenery table (for example, Beijing Great Wall, Beijing Summer Palace, Sichuan wide and narrow lanes, specific table fields are associated with city table)

main table
there should be a plan_content field in the main table to store the information selected by the above users. Note here: the actual hotel or scenic spot is stored, not just the hotel code and scenic spot code.

see your other responses above, remember that hotels, scenic spots and restaurants should not be classified as a project table. Query through a certain logo, first of all, this kind of design will make the table data large and not easy to deal with, and secondly, the cost of table maintenance will also increase. Finally, in the era of big data, all the information was stored in a table, which was actually looking for "death".

set up 3 tables
Table 1 (primary key, current x day, table 3 primary key, table 3 primary key)
Table 2 (primary key, table 1 primary key, itinerary information)
Table 3 (primary key, name, time, category) [accommodation information]

first of all, you have to abstract the business.

if you want to combine different businesses such as transportation, scenic spots, food and accommodation harmoniously in one order, you can abstract them into commodities. The table structure can be designed as follows:

:
id, order_no, all_price, contact_name, contact_mobile ...
:
id, product_no, order_no, price, product_type, product_info, start_date_time, end_date_time

order_id, product_id

in this way, you can use different product_type to represent different goods, as long as you add product_type, you can easily access new products.

in fact, this problem can be like this. First of all, there are basic tables
such as
1, scenic spot lists including scenic spot introductions, scenic spot tickets, etc.
2, traffic tables
3, catering tables
the most important thing is to aggregate the information of these tables into an itinerary. I think your confusion lies in how to store the itinerary of uncertain items, which can be
one or split into each day. Insert a piece of data when the user establishes the itinerary.
2. Turn the whole process into a piece of data, for example, the process is an object of json, no matter how many days, no matter how many items in each day, all are changed into strings and stored in

two basic requirements when designing a data table:

1. meet requirements
2. meet performance requirements when appropriate

the two tables should meet the requirements.

1. itinerary master table (itinerary ID, user ID, start address, destination address, departure date, creation time, etc., other fields can be added, such as remarks, etc.)
2. schedule information (schedule ID, itinerary ID, day, type (meals, hotel), type parameters (JSON,mysql5.7 support), support filtering)

because there are dynamic types and the parameters of each dynamic type are different, JSON, is used to meet the filtering requirements

1. itinerary ( id , day_sequence , date , route , serial ); (id, day, date, route, current itinerary number-it is recommended to create this field to avoid using the same id-- itinerary number for multiple days), and other fields that can find this itinerary, such as user information .
2. Traffic schedule ( id , serial , time_spend , comment ); (id, itinerary number, time-consuming, details: Shanghai to Hangzhou as a tourist bus)
3. scenery list ( id , serial , locate , comment , url ); (id, itinerary number, scenic spot name, tour notes: browse the West Lake, photo address) you can enter multiple scenic spot information here, which is consistent with the itinerary number serial.
4. Food and Beverage list ( id , serial , meal_type , restaurant , comment , url ); (id, itinerary number, breakfast, lunch and dinner enumeration, restaurant, restaurant description, picture address)
5. Hotel list ( id , serial , city , hotel , comment , url ); (id, itinerary number, seat city, hotel name, description, picture address)

in addition, if you want to record the itinerary, add a time field to each of the above tables to sort and display it after the week.
the image address can store multiple pictures, and the image address defines the separator.
disadvantages: join table query a little bit .