How do I store a list in MySQL

Question

I'm developing a website and I'm new to mySQL, so I'm not sure on the best way to go about this.

I need to store arrays of arrays of coordinate pairs (3d arrays). There's no upper limit to how many coordinate pairs are in each subarray, nor how many subarrays are there in each array. I will always be accessing an entire array at a time.

Each subarray also has two different strings that accompany them.

If I had a table that contains all coordinate pairs would it not be inefficient? Especially since I will always be looking for an entire set of the coordinates when querying, and I would never need to query for an individual pair of coordinates?

Here's an example of such an array for clarity:

[
  ["label1", "path1", [10,5], [100,40], [90,70], [50,2], [900,7] ],
  ["label2", "path2", [30,15], [17,54], [19,12], [33,22], [83,74], [34,4], [93,6] ],
  ["label3", "path3", [47,93], [9,56], [69,70], [47,5] ]
]

Answer 1

You would store this as two tables in SQL. I am making up the names.

create table paths (
    pathid int auto_increment primary key,
    label varchar(255),
    path varchar(255)
);

create table coordinates (
    coordinateid int auto_increment primary key,
    pathid int not null
    x int,
    y int,
    constraint fk_coordinates_pathid foreign key (pathid) references paths(pathid)
);

You would then construct the "array" or components that you need.

Now that said, if the array is really an object that you are using only on the application side, then you might want to store it as a single object. Of course, this sort of begs the question of why you want to use a relational database for this. I much recommend storing the data as proper SQL tables. But the facility is available:

create table arrays (
    arrayid int auto_increment primary key,
    arraystuff blob
);

I'm using blob as an exaggeration. Often JSON or a text string would be appropriate. Actual bit representations are only useful in certain circumstances (such as images).

You can store the representation that is actually used in the application -- or really a serialized version of it. However, you get no SQL functionality from this, such as being able to count the number of coordinates or fetching rows that only have a certain name.