[thelist] Architecture for arbitrary data management
Joe Flintham
list at menticulture.com
Fri Feb 29 09:54:27 CST 2008
r937 wrote:
>> Basically, I'm trying to design a system which can cope with storing
>> a fairly arbitrary set of key:value pairs, but which can quickly
>> retrieve subsets of them based on complex queries as in any RDBMS.
>>
>
> be very careful, this is a slippery slope and you will end up frustrated and
> in tears, because retrieval is very complicated and anything but quick
>
> see:
Thanks rudy, this is exactly what I feared, and hence I'm wondering
whether there is a more appropriate way of doing it (as in Katz's
CouchDB system?) than using an SQL db. I particularly took note of this
warning:
<tip type="Database design">OTLT and EAV are “generic” approaches that
are seductive to programmers, but are actually a bad idea for most
databases. Resist the temptation!</tip> And being a programmer, rather
than a DBA, I appreciate the warning!
In the first article you cite, the author proposes a second model
('Exploded schema') before laying into the EAV model. Assuming there is
a way to solve my problem in an SQL db, let's imagine that the users'
imaginations run out of new attributes (or keys in my key:value schema)
at, say 100 types. We'd end up with potentially joining up to 100 tables
in a query. Does that lead to hell too, or is it at any rate the least
worst option? It'd mean creating a table any time a user want to create
a new attribute, but would preserve data integrity. I actually started
prototyping this approach, but I don't know enough about database
performance when operating on so many tables, so thought I'd better hold
off, pending expert advice :)
Thanks
Joe
More information about the thelist
mailing list