I have a complex problem to be solved, I have several excel files containing data from over 1200 counties from 56 states. Problem is that most of the counties has some columns which are different from rest of the counties. And one county may be in more than one state and having different columns. I have to upload these excel files to Database from web application.

Tried Solution So far Probably the worst one: I have flat tables for each county, on upload user selects county and state and data is inserted to respective county flat table, then a procedure for that county is executed which picks data from this flat table and inserts into to a single table for all these counties. I have a separate flat table and procedure for each county.

Deep down I know that this is a worst kind of solution i came up with. Any help or guidance will be highly appreciated.

Sample Data screen Cast enter image description here

  • 1
    Could you give some examples for the type of data per county (like population?). Also (my own curiosity) which county belongs to several states? My first impression would be that something like state <-> county <- attributes would work better. But would you for example need to know the source of the data (name of excel file) later or have regular updates from new files? Sep 9 '16 at 8:06
  • type of data is property and it's attributes e.g owner name, tenants, address etc. jackson county belongs to Alabama, Arkansas and several other states. There are regular updates with new files. Sep 9 '16 at 9:49
  • 2
    Has Jackson county in Alabama any relation with Jackson county in the other states? Or are it different instances just named the same? In the latter case you can use some other property to identify counties (e.g. name&state, zipcode, or database id). Sep 9 '16 at 12:51
  • Are the definitions of concepts with the same name the same in all counties? E.g. one county may define tenant as the head of a family that rents a building, an other may define all adults as separate tenants, and the third may include children as tenants. Sep 9 '16 at 12:55
  • @KaspervandenBerg has a point. Jackson county in Alabama is not the same county as Jackson county in Michigan. They share the same name, but are different government entities. Sep 9 '16 at 15:33

You can use something besides the county name to identify that county: name&state, zipcode, or database id. This would solve the problem of two counties with the same name.

Define a set of concepts that fit (most of) the data you receive and the information you wish to extract from the data. You can either define your own concepts, use common concepts found in the data, or use a data model from a central agency such as the US Census Bureau.

When a county's data model defines additional properties that you wish to preserve, you have some options:

  • You can use specific tables that link to the main tables

    One table per coherent set of county specific properties. If a few counties share a set of coherent additional properties all counties share a single table for that data; no two tables with the same columns for multiple counties.

  • you can look into the Entity–Component—system-pattern[2] often used within the game development community:

    • Coherent sets of columns of data would by components;
    • the data from a single county at a single moment in time would be an entity;
    • systems are less relevant in your situation, you definitely don't need a main 'game'-loop that animates all systems, perhaps computation of information that relies on certain certain coherent sets of properties can be seen as a system (I'm not sure that viewing these computations as systems will help you).

Be careful of data that looks the same (i.e. the same column name in the excel files) but that has different meaning. This can occur both among counties and among data of different periods from the same count (in this application domain definitions of concepts change over time).

Having a common data model you can start the daunting task of converting county (and time) specific data from the uploaded excel files into your common model. Hopefully most of the data definitions are share between many counties. You do not have to convert all data at once. Version 0.1 of your software may use the most important data that has a definition shared by many counties. Later versions can refine the conversion adding conversions for less common data and perhaps reinterpreting existing data differently. Every time you release a new version, reconvert all existing excel files to receive the improved data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.