I watched the google? video presentation DRH gave (very nice, thank you). I was
interested to hear him suggest using SQLite Tables as persistent storage for
Objects/UDTs. I want to do this for an application that has a very well defined
UDT that is used throughout the application. It would make sense to simply
store the UDT array locally using SQLite.
The part I am not clear about is how to integrate this with the main database
on the server. This database is a larger superset of the local database and
requires all the elements of the UDT be broken out into a seperate table, one
per column, so that a query can be made against the individual elements. The
return would be the rows of the UDT table NOT all the individual elements.
Lets say the main server database contains things like, name, address, contact
info, personal metrics (like height and weight) etc etc for a total of say 60
fields.
The client will issue a query for all the Rows of people over a certain height,
under a certain weight and living in one of 13 specified zip codes. Easy, but
instead of returning all 66 fields I want to return the UDT for the records.
This implies the second UDT table that has two columns?, a foreign key and a
UDT Blob for each Row of the primary table. To take it a step further, the UDT
would be compressed to minimize xfer time and server memory overhead.
I do not have a lot of experience with SQL but it seems a query would contain a
JOIN to include the UDT Table?
Does this design have any unforseen problems thus far?
The next part of the problem is that new data Rows are updated with say 10
additional fields. For example the address is processed to find the Zip+5 code
(an existing field created by the initial create string) Calculations are
performed to determine other field values.
After each one of these steps the corresponding UDT table must be updated. This
would require pulling every fragment of data out of the Row in question from
the primary table, populating the UDT and then upddating the corresponding Row
in the secondary UDT table.
This is not a problem so much as an implied step with this method of using a
Table for the UDT storage. (comments?)
Finally there is the choice of the Primary Key.
In my (inexperienced) thinking, I would like to use something other than the
RecID (INTEGER PRIMARY KEY) as this is more database dependant than data
dependant.
There is a field in the data that is unique, it is a combination of numbers and
letters and dashes, but it is missing in one in a thousand records
unfortunatly. I could create a fake one, but then I run the risk of a duplicate
later.
So I was thinking of using Date (Julian format) to generate a base number (all
the records have some date attached to them) and then use something else (???)
added to that. That would provide a convenient INTEGER Primary key
I could Hash the record and use that. This would provide a unique 32byte binary
hash. I would choose the fields to make the hash carefully, but if two records
have empty fields for all that I chose, I no longer have a unique Key.
I could use a random number generator and simply check to see if the new random
number has been assigned before... but I might as well use the RecID.
Now I suspect you may be thinking "just use the RecID" because it is guaranteed
unique. I could. But the first batch of data will be chronologically after the
final batch of data. So all my RecID's will be counter intuitive. (not good at
2am when some disaster has to be handled)
Also, if I transfere the database to another system down the road, that
database will be creating its own RecID unless I force it to use the existing
RecIDs. Its just messy. I would rather create a Data driven Primary key, that I
also use in the application, that is independant for the RecID used by the
Database.
I would appreciate any thoughts on this from this group. I am sure none of
these issues are new to you guys!
---------------------------------
Never miss a thing. Make Yahoo your homepage.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
