CODEWITHSUNDEEP
javaandroidsql
Skraaln Skraaln
Skraaln Skraaln

Reputation: 41

Storing music tags in an SQLite database

I'm looking for an efficient way to store music tags and comments in an SQLite database running on Android API 8 (Froyo 2.2). They should be in a similar format to Vorbis comments.

So far I've considered:

A JSON Object stored in a column named 'fields' formatted similarly:

+-----+----------------------------------------------------------------------+
| _id |                                  fields                              |
+-----+----------------------------------------------------------------------+
|   0 | {"artist":"Artist", "title":"Track", "album":"Album 123", "track":3} |
+-----+----------------------------------------------------------------------+

Individual columns for each field. This is the way google.android.music stores fields, but I was worried about new tracks adding too many columns. Source:

On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table. http://www.sqlite.org/limits.html

+-----+-------------+----------------+------------+
| _id |   artist    |     album      |   title    |
+-----+-------------+----------------+------------+
|   0 | Test Artist | Test Album 123 | Test Title |
|   1 | An Artist   | This Album     | This Title |
+-----+-------------+----------------+------------+

Originally I just put them in a

Map<String, String> and invoked toString(), adding it to 'fields' as follows:

+-----+--------------------------------------------------------------+
| _id |                            fields                            |
+-----+--------------------------------------------------------------+
|   0 | {title=Example Title, album=Example Album, artist=An Artist} |
+-----+--------------------------------------------------------------+

This is similar to a JSON Object (the first example), but with added difficulties, such as parsing it back into a Java object.

I would prefer having the option of an arbitrary number of fields, so I'm tending towards a JSON Object. Please tell me any alternative options, or what you'd do in a similar situation. Thanks!

I created the ASCII tables with http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

Upvotes: 4

Views: 861

Answers (3)

Vinay S Shenoy
Vinay S Shenoy

Reputation: 4028

Have you heard of NoSQl databases? It will be a perfect fit for your needs since it stores data in a JSON Model, so each data item can have arbitrary fields. Check out TouchDB-Android.

Upvotes: 1

Madbreaks
Madbreaks

Reputation: 19549

I would strongly suggest normalizing your data - don't store JSON, etc, because it drastically reduces your ability to later query that data in any meaningful way (and believe me, NOT normalizing data seems to be the most sure-fire way to guarantee that you will need to query that data in the future, even if you don't now).

Go with your second example of multiple columns, you will be glad you did.

Upvotes: 2

Robert Harvey
Robert Harvey

Reputation: 180868

Start with individual columns for each field. If you have a need later for several user defined fields, you can always add a json object later, but my guess is you won't need it.

Individual fields have many, many advantages, including the ability to sort and filter on any field. Most sql database abilities are based on having individual fields, so it will be much harder to do anything if you combine the fields.

Upvotes: 1

Related Questions