CODEWITHSUNDEEP
mysqlsqldatabase
anthius balaraw Xanthius
anthius balaraw Xanthius

Reputation: 163

How do I store a list in MySQL

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] ]
]

Upvotes: 13

Views: 25117

Answers (3)

Piyush Juneja
Piyush Juneja

Reputation: 21

From what I've understood after reading OP's post & the rest of the thread, I think it may be worthwhile for you to look into the JSON data type which is supported by MySQL.

Since you are using Javascript on the application side too, using the JSON document to store your coordinate-pair array (& the tags) should help keep things fairly consistent too. Not to mention the flexibility which comes with storing information in a JSON object.

Here's a link to a quick reference to the JSON data type in MySQL.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269653

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.

Upvotes: 3

InzeNL
InzeNL

Reputation: 82

You might never have to query for individual pairs of coordinates, but you still have to store them individually. Because of the variable amount of coordinates linked to a subarray, you can't save them directly in columns (or you would have an infinite amount of empty columns). This asks for a seperate Coordinate table, which you would link to a SubArray table using foreign keys.

Now, you also want to store the subarrays in an array which asks for an Array table. Again: the amount of subarrays is variable and thus you can not store them in columns.

I'd advise to use label and path as the primary keys of the Coordinate tables, but you could also have a surrogate key in the form of an integer. Whatever you like best.

I fiddled around a bit, what this could look like. You can see that here

Upvotes: 2

Related Questions