3 ms·
Ask HN: Database design – many small tables vs. fewer larger tables
When designing a database schema, it's really tempting to create lots of small tables. This gives you a feeling of flexibility and future-proofing the database design.
But is it better to create fewer, but larger tables rather than lots of small ones?
Or is this an impossible question to answer without seeing the data that will populate the database? (Or knowing how large the dataset is?)
- ktpsns 7y agoI guess your question boils down how you deal with optionality of fields. If all columns are required, a single table is more clean. If columns are optional, it makes sense to have a separate table for it. Furthermore, as you name it, once you want to extend your binary relations to triplets, for instance, the seperate table will pay off. However, a DB schema should be written for being optimal today and not in a nonexisting future.
- lun4r 7y agoin most RDBMS it would be better to have fewer larger tables. you could use its internal partitioning if it has one. one problem you'll run into with many small tables is stuff like 'open_tables' limit (mysql) and that the cache efficiency declines. stick with built-in partitioning/sharding to avoid trouble.
- masonic 7y agoOnce you've reached 3NF, stop there. Sometimes targeted denormalization can allow better performance.