
Ask HN: Database design – many small tables vs. fewer larger tables - vanilla-almond
When designing a database schema, it&#x27;s really tempting to create lots of small tables. This gives you a feeling of flexibility and future-proofing the database design.<p>But is it better to create fewer, but larger tables rather than lots of small ones?<p>Or is this an impossible question to answer without seeing the data that will populate the database? (Or knowing how large the dataset is?)
======
masonic
Once you've reached 3NF, stop there. Sometimes targeted denormalization can
allow better performance.

------
ktpsns
I 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
in 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.

