Sqlite schema for android application
I am developing an Android application that queries various statistics from a static on-phone database about cars, such as acceleration, engine displacement, doors, etc. I need to specify the format of the database from the client. The UI needs to be able to show a sorted list of information for a particular attribute from multiple cars, such as engine displacement, or cost, AND another view that shows all the attributes for a particular car.
My first thought, rather than creating a table with a column for each attribute: (which is what, 1st normal form?)
Cars:
ID - integer primary key
Make - text
Model - text
HorsePower - Real
Acceleration - Real
Doors - INteger
etc..
Do this instead:
Cars
ID - integer primary key
Make - text
Model - text
CarAttributes:
ID - integer primary key
DisplayName - Text (that gets shown as a column header, or for a line)
Unit -开发者_StackOverflow Text (a short unit suffix that gets appended to the value such as mph)
Values:
ID - integer primary key
CarID - foreign key
CarAttributeID - foreign key
Value - Real
This allows the view, given the CarID and the AttributeID to perform a couple queries and just display the results. With the first form, the view would have to somehow know what units/display name goes with what columns.
Is this a reasonable way to request they send me the schema? In other words, if you had a request to provide the data in that schema would you say "ooh gross!"?
Edit: Added example of first schema, and provided more clarity.
I haven't worked with Android, but I've worked in automotive aftermarket database publishing. Typically the feature-attributes are attached as child records to the SubModel entity, and you have something like this:
MAKERS
makerid int
makername varchar
MODELS (e.g. Honda Accord)
modelid int
makerid int
modelname varchar
SUBMODEL (e.g. Honda Accord Sedan, Honda Accord Coupe)
submodelid
modelid
submodeldescription
yearintroduced
MAJORSYSTEM
majorsystemid
majorsystemname varchar
FEATURE
featureid int
featuredesc varchar
majorsystemid int
SUBMODELFEATURES
submodelid int
year int
featureid int
In a OODBMS, you can have MODELFEATURES and allow them to be overridden at the SUBMODEL level, just like class inheritance.
精彩评论