Hi folks,
I'm working on a project where there is lots of data that I need to store and use for lookups. An example of this is unemployment rates by county for the entire nation for the last ten years. Its a lot of data points and I'm trying to figure out the best way to handle it in Laravel.
Here are the options I think I have. Can anyone suggest better solutions or confirm that one of these is good and comment on whether its architect-ed right or not?
Thanks in advance to all who post!
Option 1: Create utility files that house arrays of data. Jefferey Way did this in one of his training videos for the state names and abbreviations. He threw them in a utility class and then injected them into the view as needed.
Option 2: Create a database table and store all the data on the table. This is how I would normally do it, but I'm concerned with the number of queries, the size of the results being returned and the speed and performance of this approach vs option 1 and pulling from the server (which has SSD drives).
Are there any other options? Also if I go with option 2, i think ideally I would want to create a seeder to load all this data at the start of each server to make sure the database is seeded with this supporting data.
Any help or advice anyone can give that might have done something similar would be greatly appreciated.
Thanks again!
I would go database. Arrays of data work fine for small things, but if youre looking at a lot of data, a database is going to more efficient and easy to deal with. I have a similar project that uses massive amounts of data and I can get most of it with a single database query. I organized it so that I have one main table (maybe country in your case) that has relationships with all the other tables. You could also use joins effectively in this situation I think.
If you put it into a file you'd have to read the whole file into memory even if you only need some of the data, vs a database which would let you just grab the parts you need.
Databases are designed for managing large (sometimes huge) amounts of data, I'm not sure a flat file would be a better solution, but without knowing what the data is, how it relates to each other, etc.. it's really not possible to answer your question.
But really you're not talking about a huge amount of data here.. there are 3007 counties in the US. To store an unemployment rate per county, per year for the past decade you're only talking about 30k rows. MySQL will handle that without breaking a sweat... SQLITE probably would too.
I wrote up how I used LOAD DATA LOCAL INFILE for the seeding of reference data. You may find it useful: https://tenerant.com/blog/using-load-data-local-infile-in-a-laravel-migration/
Wow. Three great replies in 10 minutes. Really appreciate it guys. I will definitely use the database method based on the feedback so far. I agree with the comment about having to load the entire file if I go the utility class route. Thanks a bunch!
@lindstrom - awesome article. that was kinda the line I was thinking I would go since these are large. Just create a tab delimited file in excel save it locally on the server and use the migration to infile the data. Great approach.
Please or to participate in this conversation.