CODEWITHSUNDEEP
androidsqlite
AndroidPlayer
AndroidPlayer

Reputation: 71

In Android SQLite, when the number of columns is more, the database size with the same data is higher

In an Android application,

Configuration 1:

There is SQLite with 3 Columns as following:
Column1:Image1
Column2:Image2
Column3:Image3~Image4~Image5~Image6~Image7~Image8~Image9~Image10

Where Image3 to Image10 are seperated by '~'

Configuration 2:

Now, If I assign separate columns for Image4 to Image10 then there would be 10 columns with the same data

Question:

The database size of Configuration 2 would be bigger than the database size of Configuration 1

When the number of rows in the database is more then the difference between the sizes is higher and considerable

Why?

Upvotes: 0

Views: 72

Answers (1)

GMB
GMB

Reputation: 222482

This is too long for a comment.

1) Storing delimited lists in a relational database is almost always a very bad idea

2) Spanning the values over columns is marginally better; basically, it makes sense if and only if the number of images per user is really fixed, like each and every user has exactly 10 images. Otherwise, you will end up with empty values here and there; and if you ever need to handle more than 10 images per user, then you need to alter the structure of your database, which is not what you want

3) I would warmly recommend having a separate table to store the images of each user in rows (not columns), with a foreign that references the corresponding user in the original table; this is a normalized design for a 1-N relationship that is much more flexible, easy to maintain and query

Of course, there is a storage overhead for adding columns or tables, but the advantages of a normalized approach should largely overcome this.

Upvotes: 1

Related Questions