CODEWITHSUNDEEP
androidsqliteschemanormalizationrdbms
nme32
nme32

Reputation: 213

Database with 14 discrete fields - structure (SQLite android)

I'm not sure how to build a database for a form where I have 14 fields with 5 possible fixed answers in each field. Record number may typically vary between 30 - 200 records during runtime. Any more than 100 records would be uncommon however. May get the occassional 400 record table.

Often a given combination of entries will be repeated, or a series of records will have -most- values in common, BUT it wont be possible to predict which of these may change between record insertions.


I would like to avoid large amounts of repetition, but retain a good degree of search efficiency if possible. Below are my proposed schemas.

Schema 1: 1 large 14 field table. Probably ok for small datasets (<30), but 200? On Android?


Schema 2: Some attempt at normalisation: break up into 14 - 2 tables with foreign keys. Not sure how to go about deciding the optimum number, other than trial and error.


Schema 3: A bit more elaborate but not hard to understand.

2 tables. Table 1 has 14 fields, where a primary key corresponds a given combination of 14 entries. Table 2 then uses foreign keys to log records according to the FK that corresponds to combination in Table 1. New combinations are populated into Table 1 as they appear at runtime.

Then use a map to check if entered record corresponds to an existing combination (also generated on fly). Map key is just sum of discreet values in fields* - is there a better way to generate map keys? I can't think of any way to cheaply generate unique keys. I'd then have to use conditionals to differentiate between combos with like sums (that's 13 ifs for matching combinations right?)

*Each state in each field as corresponds to a numerical value.

Is this schema (3) likely to scale well over my prescribed range, or is it a bad idea / overkill / totally inappropriate?


Is there a better way to go about this altogether?

This is my first time working with RDBMSs and I'd really appreciate any help you can give.

EDIT: Also forgot to mention this is part of a larger database with 32 fields in total, some discrete and some strings, which are much easier to predict the variations of or otherwise deal with....

Upvotes: 0

Views: 212

Answers (1)

dcow
dcow

Reputation: 7975

My intuition is that you won't need to worry about the number of records being large until you hit thousands/tens-of-thousands of records. Really, you can think of a database with 400 records as a 400 line text file. Android can easily parse gigantic xml/json files well into the 10 to 100 thousand lines long. 30 to 400 records should be perfectly acceptable for Android.

If I understand schema 3 correctly, you plan to keep 5^14 preconfigured states and then have entries in the main table reference a given state configuration in the selection possibilities table? This sounds like overkill since 5^14 = 6103515625 (this is worst case of course). You would [possibly] get noticeable memory usage reduction by choosing schema 3 if there will be lots of duplicated states, but it sounds like this is an unnecessary and early optimization.

In general, plan a system that works well first, then refactor if necessary. Don't try to write the most exhaustive and elaborate scheme from the start (database or coding, it doesn't matter).

It will probably be most efficient to go with the simplest schema: schema 1. If you get to the point where record count becomes an issue, refactor later to something like schema 3.

Or, find a way to pack those bits in. 5 options, 14 states, 2^3 holds 8 states, 3 * 14 = 42 bits. You could generate a tree as all the selections come in and traverse it in 3 bit stages. The tree would not fit in memory of course so you would need to break it up and find some way to serialize it..

Upvotes: 1

Related Questions