[sqlite] Re: DB design questions

[A. Pagaltzis]

Hi Michael,

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-21 22:35]:
> Thanks for your response. Do you have a recommendation for a
> simpler data store, which supports only simple queries (like,
> equals, not equals on attributes) and transactions?

BerkeleyDB might be a candidate. It only stores key-value pairs,
but keys may have multiple values, and it’s easy to come up with
some convention for composite key names in order to store more
complex objects. (If need be, you store a list of keys under
another key or some such. Depends on what you want to do.) It has
transaction support and as a bonus, it’s much faster than SQLite.

(SQLite is significantly slower than many simpler datastores such
as BDB. The benefit is that you get to write arbitrarily complex
queries abstractly in SQL rather than having to spell them out as
scads of imperative data structure examination code, that then
also has to be debugged and maintained.)

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] Re: DB design questions

[Michael Ruck]

Thanks for your response. Do you have a recommendation for a simpler data
store, which supports only simple queries (like, equals, not equals on
attributes) and transactions?

Thanks,
Mike

-Ursprüngliche Nachricht-
Von: A. Pagaltzis [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 21. April 2007 21:17
An: sqlite-users@sqlite.org
Betreff: [sqlite] Re: DB design questions

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-20 16:15]:
> Is there anyone who has experience with this kind of design, do you 
> have better ideas on modelling this kind of data?

This is actually a very typical approach to storing arbitrarily structured
data entities in an SQL database that everyone discovers independently, much
like the adjancecy model is the first thing anyone comes up with for storing
trees in an SQL database.

The problem with this sort of schema (just as with the adjacency
model) is that it makes it very hard to formulate any kind of interesting
query over the data. You’d need a vendor-specific facility for recursive
queries in order to ask anything non- trivial of the database, but such
queries are expensive even where supported, which in SQLite they’re not.
Essentially, you are reducing the SQL engine to a dumb backend store
incapable of complex query logic; complex queries have to be performed in
application code after retrieving the entire set of possibly- relevant data.

You’re better off using some other kind of data store than an SQL database
if you really need storage for that kind of model.

Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: DB design questions

[A. Pagaltzis]

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-20 16:15]:
> Is there anyone who has experience with this kind of design, do
> you have better ideas on modelling this kind of data?

This is actually a very typical approach to storing arbitrarily
structured data entities in an SQL database that everyone
discovers independently, much like the adjancecy model is the
first thing anyone comes up with for storing trees in an SQL
database.

The problem with this sort of schema (just as with the adjacency
model) is that it makes it very hard to formulate any kind of
interesting query over the data. You’d need a vendor-specific
facility for recursive queries in order to ask anything non-
trivial of the database, but such queries are expensive even
where supported, which in SQLite they’re not. Essentially, you
are reducing the SQL engine to a dumb backend store incapable of
complex query logic; complex queries have to be performed in
application code after retrieving the entire set of possibly-
relevant data.

You’re better off using some other kind of data store than an SQL
database if you really need storage for that kind of model.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-