SQLite Forum

Should tables be populated based upon a known/expected pattern of retrieval?
Login
I feel I should add some notes, before someone thinks that there is no good reason to have multiple tables with similar structures EVER.

Reasons to split tables in the face of those disadvantages mentioned in the parent post:

- Physically the data won't fit in a single file/disk/partition.
  In SQLite's case, this will require a separate DB, not just table.
- The data is split upstream by some factor unrelated to the content. The data for different regions/states/counties may reside on different web servers/services or be accessed by different front-ends so that they will never mix and look-ups will never ever happen outside a specific set.
- For SQLite specifically, or ISAM format tables supporting only table-level locking and not row-locking, very high write-concurrency can be improved/solved by multiple similar tables, at the cost of having to Join/Attach them when doing wide queries.

In near every other case it is better to have a single table. (I might be forgetting a use case or two - perhaps someone else might add more).