Saving a list of strings in sql

Question

In our system we have an API call that returns a list of stations. Each station among other things has a name and a corresponding code. For example, the response looks like:

[
   {
    name: "New York C",
    code: "0001:074"
   },
   {
    name: "Oslo C",
    code: "0002:078"
   },
   ...
]

This list is quite big and contains approximately 3500 stations.

What we need to do is to create a widget that can be configured to have max 50 stations to choose from. These stations are a subset of those that are returned by the above mentioned call. Basically, we don't even need to save the names, just codes will be enough.

The question is how do we save the subset of stations (codes) in DB?

I know about 1NF and I have read this how-to-store-a-list-in-a-column-of-a-database-table.

The thing is that there is no need to import all 3500 stations and put to the database because we have access to the call from the widget. But we still need to save the configured subset of data.

Any help would be appreciated.

UPDATE: my question is not HOW technically but more like but HOW design-wise. I could save it as a comma separated string but then I guess it's not 1NF. So basically you say, it's ok to create a separate table for user-selected stations even though we don't store the main table with all possible stations. Or? — acy, Feb 08 '16 at 16:30
If you are talking about one table and only one, so a table `Stations` with name and code fields is 1,2,3 NF. — Haytem BrB, Feb 08 '16 at 17:05

score 0 · Answer 1 · answered Feb 08 '16 at 16:20

Your "list" is in JSON format, and you can export it to any database (let's assume it is MySQL):

So you will have a table called "Stations" for example in your DB, and your table will have two columns: name and code.

To export your Json data to MySQL, you need to export it to CSV first, using: http://www.danmandle.com/blog/json-to-csv-conversion-utility/ or https://github.com/danmandle/JSON2CSV

Then export your CSV file to MySQL using: Then :

LOAD DATA INFILE 'filepath/your_file.csv' INTO TABLE Stations;

There are plenty of ways to achieve the same result, you can for example use PHP to do that: http://www.kodingmadesimple.com/2014/12/how-to-insert-json-data-into-mysql-php.html

Or using an ETL (like Talend, SSIS...).

Saving a list of strings in sql

1 Answers1