[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