CODEWITHSUNDEEP
c#databasesqlite
Ross F
Ross F

Reputation: 67

SQLite, multiple tables with same columns or one large table? What is better?

I'm currently writing an exercise history tracking app in c#(ill port it to mobile someday with xamarin) using SQLite. Every time an exercise (E.G pushups, situps etc) is completed I want to store that with a whole lot of other information (time, location etc) in a table.

Should I store ALL the history data in on table and then use Id's or something to separate each exercise, or should i store each exercise in its own table? Which method would be faster and more efficient and will choosing one method bring me trouble later on down the track?

It is highly likely that each exercise will always have the same columns, and as a worst case scenario, I could have up to 100 different exercises with say 2000 history entries. The plan is to plot the statistics of this data to show to a user

Thanks in advance

Upvotes: 5

Views: 2086

Answers (1)

Madalin Grigore-Enescu
Madalin Grigore-Enescu

Reputation: 1270

Experienced database programmers are usually missing a very important SQLite particularity: ONE single database file.

This particularity creates some VERY unexpected SQLite single table behavior that makes SQLite single table approach something that you should not superficially dismiss just because generic relational database theory teach you not to do it.

  1. Overall storage is always cheaper with a single table instead of multiple tables. Overall file reading and writing speed is faster.

    Because SQLite is using one single file to store the entire database (you can create different files only if you open different databases for different tables), SQLite does not behave like others traditional databases that are using one or more than one file per table. That's why sometimes the performance of a single table beats the multiple table approach especially for disk IO related operations.

  2. In SQLite the payload, either table b-tree data or index b-tree keys, is always in the "record format". The record format defines a sequence of values corresponding to columns in a table or index. The record format specifies the number of columns, the datatype of each column, and the content of each column. The record format makes extensive use of the variable-length integer or varint representation of 64-bit signed integers. These varints are called "serial type". Space used by a serial type NULL is 1 byte. Having many columns that holds NULL in SQLite is very cheap. This may encourage you to store many columns into the same table, especially if some of your rows are optional and you maybe traditionally tempted to write them in a separate table only when they are present (for example: 1 to 10 different optional properties of an object).

Upvotes: 6

Related Questions