Strategy for regularly updating datasets from excel files

database-design

I have ~10 excel files which are produced by a third party and updated each night. I've have a script that downloads them to my local machine. They contain ~ 10 fields (all short text / dates) and between ~10,000 and ~1m rows in each.

I'm planning to create a simple web application to enable people to search the data. I'll host it on AWS or similar. Search load will be light maybe ~1000 searches / day.

I have to assume that all the records are unique each night and need completely replace the online dataset.

It's relatively simple for me to convert the data from the excel files into a database such as Postgres and create a simple search on top of it.

My question is how do I deal with the time it takes to do the database update each night? Should I create two databases and have my application alternate between them every other night?

What is a typical strategy for dealing with a situation like this?

My current skill set is Ruby/Rails/Postgres building and simple web apps. But I've been intentionally vague about technology because I'm open minded about what to use. And I'm quite happy to learn something new to solve the problem.

Best Answer

One method is to have two sets of tables (This1, This2, That1, That2, etc.), or a set of tables in separate databases/schemas. Either way, you now have SetA and SetB. Have a set of views that access one set of tables, say SetA. Perform the nightly imports to SetB. When the new data is ready to go, recreate the views, pointing them now to the SetB tables. The next night, use SetA to import.

The views will act as a software switch with an unnoticeable switch-over rate. And it should be dead simple to add this to existing Ruby script to coordinate all of it.

Related Solutions

How to manage a list for particular object. for eg. user comments on specific object(user post etc.)

As the comments indicate, you need to understand normalization. Without too much theory, when you want a list for a particular object in a database here are some possibilities:

Create a new column for each additional list item. If your list has a small concrete length this may be a viable option, however for most lists n items today becomes n+1 tomorrow and your table begins to have an unmanageable number of columns that require extra coding for joining and filtering.
Create a new table for each additional list. If the lists are independent then this is the way to go, however, if the lists are the same, then creating and maintaining a new list table for each and every object becomes unmanageable. Additionally, queries crossing objects require UNIONs on multiple tables. These queries could become lengthy and have to be modified with each additional object.
Create a normalized table for this type of list. If the list structure is the same for each object then a single table can be used to store any number of list items for any number of objects. The tables definition should be defined and scoped.

For your particular question you have defined the list item as a comment, so you will simply need additional columns to indicate what object (post?) it goes with and what comment number it is. You may also want to have columns indicating who made the comment, when it was made, and from where it was made.

Here is how this could look:

Post    CommentNumber  Comment                                     
------  -------------  --------------------------
098763  1              Comment 1 for post 098763.
...  
142525  1              Comment 1 for post 142525.
142525  2              Comment 2 for post 142525.
...
200526  1              Comment 1 for post 200526.

Mysql – big db for managing user files and settings

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas. Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

Related Question