I'm starting work on a project where I'll have a table of entries as well as a table of descriptors.

The entries will be updated infrequently, if ever, and the descriptors table will be largely static. Each entry will have approximately 5-25 descriptors linked in an associative table, though it could be more or less. Each entry has five core columns and another 20-ish nullable columns for optional metadata, mostly tinyints, though one is a free-text input that I currently have as VARCHAR(4096). I would expect an average of 5-10 of these columns to be completed per entry, though none are mutually exclusive, and thus it could be all or none.

Should I leave the 20-ish metadata columns in the entry table, or should I make integer-based keys for each and put them in a separate table? How significant would any performance difference be? I realise that this may be premature optimisation, but I'd rather avoid potential headaches down the road.

  • That's basically a variant of the infamous EAV approach. Whether you want to go in that direction depends on what kind of queries involving entry metadata you are going to run and how you'd like them to perform. Consider that each reference to a metadata column, if the values are in a separate table, requires a join. Search this site (and elsewhere) for "EAV" and enjoy the controversy.
    – mustaccio
    Commented Nov 16, 2016 at 20:11
  • Right, exactly, I'm just having difficulty finding real-world performance data, even if only anecdotal. If it was <10 or >100 columns, I think it'd be pretty clear, but I feel like my specific case is in the grey area. Commented Nov 16, 2016 at 21:28
  • 10 rows of 100 columns -- no performance problem. A million rows with any kind of EAV -- problem.
    – Rick James
    Commented Nov 17, 2016 at 2:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.