acy February 2016

Saving a list of strings in sql

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.

Answers


haytem February 2016

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...).

Post Status

Asked in February 2016
Viewed 3,977 times
Voted 12
Answered 1 times

Search




Leave an answer