How to handle variable-length arrays in a relational database

Question

I'm creating a booking system for a shuttle service, that allows users to book shuttles for employees, specifying an origin, waypoints, and a destination. The waypoints are the aspect I'm particularly interested in. The origin and destination are non-nullable, but the waypoints are optional, allowing a variable number of entries (0..*). Users will have a dashboard that will allow them to edit trip details in future, meaning they could possibly delete or update waypoints. I'm not quite sure what would be the best method of going about storing a variable-length array in a relational database, particularly one using MySQL as an engine.

I've explored the various ways of doing something like this. One option is to just create a waypoints field in the booking table that stores a JSON object, which is possible because I'm using MySQL. This works, but I'm just concerned updation may become an issue in future. Alternatively, I could create another table named 'waypoints' that, for each row, stores a waypoint along with the booking reference as a foreign key.

Answer 1

Here are a few "best practices" based on what you have said:

• "variable number of entries (0..*)" ==> need another table.
• "storing a variable-length array" ==> need another table.
• Learn how to connect two tables in a "one-to-many" relationship
• Learn how to connect two tables in a "many-to-many" relationship by using an extra table.
• Don't put data into JSON that will be used in a WHERE clause.
• Do put miscellany data in JSON if the application is the only place where the code will look inside the JSON.

You seem to be too new to dbs to discover all the "best practices from the get-go". Put something together. Then present the table definitions and a few of the queries here (in a new Question) for critique.

Answer 2

Rick James's answer is the more general answer that other users should refer to for the problem referenced in the heading.

Though for the specifics of my problem, this is how I ultimately went about doing it. The locations I store aren't just strings containing addresses, but are rather objects with multiple properties. For now, I store a main_text (a short address as a quick reference) and address (complete address to be used to identify the specific location) value. So I created a location table with a place id, booking id as the foreign key, main_text, and address. The origin and destination in the booking table then reference places in this this table.

The waypoints are stored in a separate table. They too are places, and so need to reference a place in the place table. So it's composed of a place id as the primary key and foreign key, booking id, and relative position in the ordering of waypoints.

What was slightly tricky when dealing with these cross-dependencies was figuring out how to cascade deletes. I initially set it up such that the booking id on both the waypoint and place table was used to cascade deletions for both tables, but I found that this didn't work. Instead, I ended up setting this cascade deletion constraint on the booking id of the place table, and then used the same constraint on the waypoint table, but instead on the place id. So when deleting a booking, it deletes all its places and waypoints.

The final schema looks like this:

• booking

| booking | CREATE TABLE `booking` (
  `id` varchar(255) NOT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `created_at` int DEFAULT NULL,
  `updated_at` int DEFAULT NULL,
  `origin_id` varchar(255) DEFAULT NULL,
  `destination_id` varchar(255) DEFAULT NULL,
  `num_passengers` int DEFAULT NULL,
  `arrival_datetime` datetime DEFAULT NULL,
  `return_datetime` datetime DEFAULT NULL,
  `num_vehicles` int DEFAULT NULL,
  `return_trip` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_ind` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• place:

| place | CREATE TABLE `place` (
  `main_text` varchar(255) DEFAULT NULL,
  `address` varchar(1000) DEFAULT NULL,
  `id` varchar(255) NOT NULL,
  `booking_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `booking_ind` (`booking_id`),
  CONSTRAINT `place_ibfk_1` FOREIGN KEY (`booking_id`) REFERENCES `booking` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

• waypoint:

| waypoint | CREATE TABLE `waypoint` (
  `place_id` varchar(255) NOT NULL,
  `booking_id` varchar(255) NOT NULL,
  `position` int NOT NULL,
  PRIMARY KEY (`place_id`),
  UNIQUE KEY `place_id` (`place_id`),
  KEY `place_ind` (`place_id`),
  KEY `booking_ind` (`booking_id`),
  CONSTRAINT `waypoint_ibfk_1` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) ON DELETE CASCADE,
  CONSTRAINT `waypoint_ibfk_2` FOREIGN KEY (`booking_id`) REFERENCES `booking` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

--- UPDATE ---
I removed the booking_id from the waypoint, and instead made it such that it's exclusively a child of a place. I actually was forced to do this because the ORM I'm using enforced it, but in retrospect, it makes sense to not store the booking id in the waypoint when the place already stores that information.