CODEWITHSUNDEEP
sqlitedatabase-designschema
Björn Pollex
Björn Pollex

Reputation: 76778

How to plan for schema changes in an SQLite database?

I am currently developing an application that will store data in an SQLite database. The database will have much more read- than write-access (in fact, it will be filled with data once, and then almost only reading will happen). The read-performance is therefore much mre important. The schema I am currently developing is very likely to change in the future, with additional columns and tables being added. I do not have very much experience with databases in general. My question is, specifically in SQLite, are there any pitfalls to be considered when changing a schema? Are there any patterns or best practices to plan ahead for such cases?

Upvotes: 6

Views: 2212

Answers (2)

Doug Currie
Doug Currie

Reputation: 41170

Here are some suggestions:

  1. Don't use select * from ... because the meaning of * changes with schema changes; explicitly name the columns your query uses
  2. Keep the schema version number in the database and keep code in the application to convert from schema version N to version N+1; then all the code in the application works with the latest schema version; this may mean having default values to fill added columns
  3. You can avoid copying tables for schema updates with SQLite version 3.1.3 or better which supports ALTER TABLE ADD COLUMN...

Upvotes: 7

Walter Mitty
Walter Mitty

Reputation: 18940

Look into data-marts and star schema design. This might be overkill for your situation, but at least it will prevent you from designing at random.

Upvotes: 0

Related Questions