1

I'm a programmer, I work on an application where our members (about 3,000) enter in data into a form on a quarterly basis. The form itself has about 120 fields, but every quarter some new fields are added and some are removed.

Is it a better for performance to have a data table with four columns (member_id, quarter_id, field_id, value) where each member adds 120 or so rows every quarter, or to have a flat table with 120+ columns, where new columns are added for new fields, and each member only adds one row per quarter?

Currently we're doing it like the latter, and we have about 67,000 records in it. I feel like the former is cleaner and simpler from a programming perspective, but it'd mean expanding that to around 8 million rows, and increasing by 350k+ every quarter.

  • writes are only done once a quarter and reads are done throughout the year, correct? – scsimon Jul 9 '18 at 15:52
  • @scsimon Generally yes, once per quarter per member. There are occasionally times when they need to edit/update their answers but not often. – David Rice Jul 9 '18 at 16:00
0

I'm with you: better to have a more flexible system than having to change your table structure every year. 8M rows is not excessive, just make sure you index it properly based on how you search/retrieve the information, and you'll be fine.

There's another way to go that would probably fit your use case better: document store databases. Switch to MongoDB or RethinkDB and flexibility issues disappear.

  • 4
    Note that you can also store JSON and XML documents in SQL Server. – David Browne - Microsoft Jul 9 '18 at 17:07
  • @DavidBrowne-Microsoft Storing JSON probably makes the most sense, and since we rarely need just a subset of fields it'd be a good fit. – David Rice Jul 9 '18 at 18:27

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.