I have two questions. I'm starting a large project
with SQLite on multiple platforms. I'm hoping I can
get some ideas about two issues:
1. Is numeric column storage maintained in an
endian-independent way? I need to port SQLite to a
platform that is in Motorola format (big endian) but
haven't done it yet. In the meantime, I'm writing the
application on an Intel format (little endian)
platform with an existing SQLite implementation. If
byte swapping of numeric column data is required, I'd
like to do it on the Intel platform because it is the
faster one. Databases will be shared between
platforms and it isn't obvious from the documentation
how numeric values are handled (or I haven't found
it!).
2. I'm finding myself in instances where I'd like to
generate a key into a table based on ID's from other
tables. The reason for this is performance. A
simplistic example would be:
a) A city table exists with two columns, "ID" (4 byte
integer) and "CityName" (text) where ID is a unique ID
for each city name.
b) A job table exists with two columns, "ID" (4 byte
integer) and "JobTitle" (text) where ID is a unique ID
for each job title.
I'd like to create another table with
"AverageSalaries" combining the ID's from (a) and (b)
into a unique 8 byte ID. I'm thinking that the
columns would be "SuperID" (8 bytes integer created by
combining each ID from the city table with the ID from
each job table and "AveSalary" which is the average
salary for that job in the given city (based on the
ID's). The reason for combining the ID's is to create
a unique ID that can be a key for quick access.
Again, this is a simplistic example but demonstrates
the issue I'm dealing with among multiple tables. Is
this combining of ID's the best way to handle this
"join" when performance is required? Can an 8 bytes
numeric key be used or must if be treated as an 8 byte
text field?
Thanks for any help. I hope to be able to provide
feedback to the list when I get more of my own
experience with SQLite.
______________________________________________________
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
