4 ms·

Image a case in which census data and the associated geometries. https://github.com/censusreporter/census-postgres https://github.com/censusreporter/census-post

Image a case in which census data and the associated geometries. https://github.com/censusreporter/census-postgres https://github.com/censusreporter/census-postgres has 22 surveys, each with 230+ tables. That's 5000+ tables right there. Now, the TIGER tables for all of that is another 50 tables per year, so another 350 tables. If these were to be partitioned by state, instead of all records for all states in a single table, then we're looking at 270,000. So, it's not _that_ difficult.

hobs 10y ago
Are the surveys so fundamentally different that I cant just store the 280+ tables with a uniqueifier for the 22 different surveys? You want it by state, sounds like a column to add to my existing tables, not a way to make hundreds of new ones, or making a table with an FK and a state column.
- logfromblammo 10y ago
  Obviously, you have not often worked with government customers. I can't even remember how many times I have explained what would be correct and maintainable, only to be told to do it exactly the same way the old system did it. So if I were working with the census, I'd consider myself lucky to not have to build a GUI for poking the holes in virtual Hollerith punch cards.
  - dsugarman 10y ago
    I worked with a small team (4 people) to rewrite the systems for determining GDP and other economic info for the US. We built an elegant system heavily utilizing dynamic SQL to keep the complexity in metadata and simplify the ridiculous amount of different ways information was collected and stored. In the end it went from taking days running ~20 COBOL programs in serial to around 20 minutes running one SQL script to get the results. I understand this may not be typical of government contracting, but it is not fair to say that you can't make quality systems for government customers.
  - josho 10y ago
    > only to be told to do it exactly the same way the old system did it. Early in my career this pissed me off too. As I matured I learned that there were often valid reasons to do it the same way. E.g. External systems accessed the DB directly for reporting, special cases meant my proposed design would be fragile, etc. To the younger folks here, when you encounter this realize constraints exist that you must workaround. If you feel strongly enough to push back then first seek out some senior resources to dig for a deeper understanding before going ahead and proposing an alternate.
    - hinkley 10y ago
      Problems that nobody talks about never get fixed. If you give up the there is no feedback and no news is good news. And one of the worst technical failures I worked on was a project where they insisted on reports from live data, and put little bits of report data in every response from the server. They couldn't grasp why the performance was so underwhelming. Every index in your tables adds time to every insert and update operation, and often slightly stale data is close enough for most users and can be pre-calculated on an interval. If you get the bright idea that measuring how many people are online or exactly how many dollars you made this month, then you will have fewer users and make less money because of it. Use reporting tables. Most people shouldn't be writing into your data anyway and if they are, run away. Make the reporting tables your external API, and then you can add or modify columns, split or combine tables whenever you want.
    - logfromblammo 10y ago
      The only reason you really need is that the customer wants it done that way. As I grow older, I become less enthusiastic about protecting other people from the potential consequences of their decisions. At this point, I will still warn about those consequences, but only so that my company will get contracted again later, to do the thing I suggested in the first place. I: You could speed up this workflow by doing X. They: No... It needs to look exactly like this paper form. I: Your wish is my command. [3 months pass] They: We want you to change this form by doing almost exactly X. Boss: [holds out hand, rubs fingers together] They: [writes fat check] I: Your wish is my command. They: Just make sure that it still looks like the form when I print it. I: [dying a little inside] Your wish is stu... still my command. [3 months pass] They: The printed forms are crap. We want you to export the data to an Excel spreadsheet. Boss: [holds out hand, rubs fingers together] They: [writes fat check] I: Your wish is my command. [continue in this fashion until your death]
  - Roboprog 10y ago
    Hah. I have been working for UC for a while on a project revamp for the state (CA). Make the table and the screen look just like form # xxx ... You got it :-)
    - phonon 10y ago
      Heh--go all the way! http://www.codeproject.com/Articles/466362/Blend-PDF-with-HTML http://www.codeproject.com/Articles/466362/Blend-PDF-with-HT...
- jimktrains2 10y ago
  The surveys can change over time. Also, the state is part of the identifier already, however, you may want to pull it out into tables for a variety of reasons.

hobs 10y ago

Are the surveys so fundamentally different that I cant just store the 280+ tables with a uniqueifier for the 22 different surveys? You want it by state, sounds like a column to add to my existing tables, not a way to make hundreds of new ones, or making a table with an FK and a state column.

logfromblammo 10y ago
Obviously, you have not often worked with government customers. I can't even remember how many times I have explained what would be correct and maintainable, only to be told to do it exactly the same way the old system did it. So if I were working with the census, I'd consider myself lucky to not have to build a GUI for poking the holes in virtual Hollerith punch cards.
- dsugarman 10y ago
  I worked with a small team (4 people) to rewrite the systems for determining GDP and other economic info for the US. We built an elegant system heavily utilizing dynamic SQL to keep the complexity in metadata and simplify the ridiculous amount of different ways information was collected and stored. In the end it went from taking days running ~20 COBOL programs in serial to around 20 minutes running one SQL script to get the results. I understand this may not be typical of government contracting, but it is not fair to say that you can't make quality systems for government customers.
- josho 10y ago
  > only to be told to do it exactly the same way the old system did it. Early in my career this pissed me off too. As I matured I learned that there were often valid reasons to do it the same way. E.g. External systems accessed the DB directly for reporting, special cases meant my proposed design would be fragile, etc. To the younger folks here, when you encounter this realize constraints exist that you must workaround. If you feel strongly enough to push back then first seek out some senior resources to dig for a deeper understanding before going ahead and proposing an alternate.
  - hinkley 10y ago
    Problems that nobody talks about never get fixed. If you give up the there is no feedback and no news is good news. And one of the worst technical failures I worked on was a project where they insisted on reports from live data, and put little bits of report data in every response from the server. They couldn't grasp why the performance was so underwhelming. Every index in your tables adds time to every insert and update operation, and often slightly stale data is close enough for most users and can be pre-calculated on an interval. If you get the bright idea that measuring how many people are online or exactly how many dollars you made this month, then you will have fewer users and make less money because of it. Use reporting tables. Most people shouldn't be writing into your data anyway and if they are, run away. Make the reporting tables your external API, and then you can add or modify columns, split or combine tables whenever you want.
  - logfromblammo 10y ago
    The only reason you really need is that the customer wants it done that way. As I grow older, I become less enthusiastic about protecting other people from the potential consequences of their decisions. At this point, I will still warn about those consequences, but only so that my company will get contracted again later, to do the thing I suggested in the first place. I: You could speed up this workflow by doing X. They: No... It needs to look exactly like this paper form. I: Your wish is my command. [3 months pass] They: We want you to change this form by doing almost exactly X. Boss: [holds out hand, rubs fingers together] They: [writes fat check] I: Your wish is my command. They: Just make sure that it still looks like the form when I print it. I: [dying a little inside] Your wish is stu... still my command. [3 months pass] They: The printed forms are crap. We want you to export the data to an Excel spreadsheet. Boss: [holds out hand, rubs fingers together] They: [writes fat check] I: Your wish is my command. [continue in this fashion until your death]
- Roboprog 10y ago
  Hah. I have been working for UC for a while on a project revamp for the state (CA). Make the table and the screen look just like form # xxx ... You got it :-)
  - phonon 10y ago
    Heh--go all the way! http://www.codeproject.com/Articles/466362/Blend-PDF-with-HTML http://www.codeproject.com/Articles/466362/Blend-PDF-with-HT...
jimktrains2 10y ago
The surveys can change over time. Also, the state is part of the identifier already, however, you may want to pull it out into tables for a variety of reasons.