CODEWITHSUNDEEP
mysqlsql
The Time
The Time

Reputation: 737

Store a big array in the MySQL table

I want to create tablestopsfor all stops with these columns id, stop_name, stop_lat, stop_long, route, arrivaltime but I dont know how can I store the arrivaltime into the table since this column is a big array

Like this:

{
   "id": 1
   "stops_name": "Amersham ",
   "arrival_time": {
                    "mon-fri": [ "05:38", "06:07","06:37",.....50 entries],
                    "sat": ["05:34","06:01","06:31",...........50 entries],
                    "son": ["06:02","06:34","07:04",...........50 entries]
                   },
    "stops_lat": 83.837994,
    "stops_long": 18.700423
 }

Is that to manage with mysql?

Upvotes: 0

Views: 148

Answers (1)

Trent Lloyd
Trent Lloyd

Reputation: 1892

Generally speaking you would split the "arrival times" out into a new table, referencing back to the table of stops. You would also generally store each time as a single row, and then select the entire collection of rows.

This works best because it lets you query on the 'time' column and search for time ranges, etc and only get the relevant rows.

For the "day", I would most likely use a Set to have a column that can be 1 or more values. Also consider that likely you may need to store info on public holidays or other special dates as well: https://dev.mysql.com/doc/refman/5.6/en/set.html

Stops: id, stops_name, stops_lat, stops_long (1, "Amersham", 83.837994, 18.700423) Stops_arrivals: id, stops_id, day, time (1, 1, "Mon", "05:38"), (2, 1, "Mon", "06:07"), etc

Upvotes: 1

Related Questions