[erlang-questions] Conceptual questions on key-value databases for RDBMs users

[Edmond Begumisa]

Hello Ahmed, Toby...

> it is a tenet of the relational model that column values
> be 'atomic' in the sense that they do not encode structure that
> would be more profitably decomposed into relations.

This is very well put. The "SQL-way" of modeling complex data is through  
multiple tables and relationships/joins. Other techniques are exceptional.

>> i believe the assumption of storing simple thing is not a general rule  
>> and
>> should not be associated with SQL. For example, postgresql provides more
>> complex data types and even user defined ones.

I'd hardly describe Postgres, a database engine that features things like  
table-inheritance, as a typical SQL-RDBMS. In many ways, Posgres has  
always been cutting edge and distinctly atypical in it's field. But you're  
right, using orders->items as an example, with Postgres, you can store the  
items in a column of an orders table. If you then want to query all orders  
that contain X-Box, you could then write something similar to...

SELECT Orders.* FROM Orders WHERE (Orders.Item).Desc = "X-Box";

This would be somewhat similar to how a NoSQL/kv-db would look at it  
(though I'm not sure how you would go about tallying the item totals for  
each order, but knowing Posgres, there must be a way.) However, this is  
NOT typically how an SQL database would be organised, even with Postgres  
that has that capability. SQL people tend to think in terms of joins and  
relationships. Instead, you'd have two tables, one with orders and another  
with items, then a similar (but not exactly the same) query would look  
something like...

SELECT Orders.*, Items.* FROM Orders, Items INNER JOIN Orders ON  
(Orders.Id = Items.OrderId) WHERE Items.Desc = "X-Box";

Complex data in the SQL world is normally modeled using  
relationships/joins. IMO, this is both SQL-RDBMS biggest strength and  
biggest weakness. Strength -- you can define "rules" for what's valid and  
invalid in your database. Weakness -- when the complex data you're trying  
to model is wide and deep, the database gets insanely complex, very  
quickly, resulting in a lot of head-scratching when querying.

Contrast with the NoSQL approach. Strength -- modeling wide and deep  
complex data is much easier and tends to map more directly to the  
application's idea of the data. Weakness -- auto-guarding against bad data  
is more difficult so you must trust the programmers.

- Edmond -

On Fri, 05 Nov 2010 09:57:22 +1100, Toby Thain <toby@REDACTED>  
wrote:

> On 04/11/10 6:11 PM, Ahmed Omar wrote:
>> Hi Joe, All
>> I'm far from being a database expert too, so feel free to correct me ,   
>> but
>> i believe the assumption of storing simple thing is not a general rule  
>> and
>> should not be associated with SQL. For example, postgresql provides more
>> complex data types and even user defined ones.
>
> As Joe must be aware, it is a tenet of the relational model that column
> values be 'atomic' in the sense that they do not encode structure that
> would be more profitably decomposed into relations.
>
> --Toby
>
>>
>
>>
>>> On Tue, Nov 2, 2010 at 9:14 PM, Silas Silva <silasdb@REDACTED> wrote:
>>>> This is a message I sent to the nosql-discussion@REDACTED
>>>> discussion group.  I thought it might be interesting to send it
>>>> erlang-questions, so here we go...
>>>>
>>>>
>>>> Hi all!
>>>>
>>>> I have used SQL RDBMSs for some time. ...
>>>> * Key-value databases are surprising simple.  I know you solve
>>>>  relationship by denormalizing data.  What data should be normalized?
>>>>  What shouldn't?  How do you update denormalized data?
>>>
>
>> On Thu, Nov 4, 2010 at 11:01 PM, Joe Armstrong <erlang@REDACTED> wrote:
>
>>> I'm no database expert so don't quote me here ...
>>>
>>> As far as I am concerned traditional databases like SQL suffer
>>> from the fact that the data stored in an individual column is  
>>> incredible
>>> simple - I can store an integer/string/... in a column but these are
>>> incredibly simple data structures. I want to store and retrieve  
>>> incredibly
>>> complicated things - how do I store an XML parse tree in a single cell
>>> of a database? - How do I store a database in a database ...
>>> ...
>>> It seems to my that SQL provides you with the ability do to complex
>>> queries on simple things. K-V dbs can do simple queries on complex
>>> things.
>>>
>>> /Joe
>>>
>>>
>>>
>>>>
>>>> Sorry for such simple and general questions.  Things were simple up to
>>>> the moment that I realized that it would be easily solved with a JOIN
>>>> SQL statement.  :-)
>>>>
>>>> Thank you very much!
>>>>
>>>> --
>>>> Silas Silva
>>>>
>>>> ________________________________________________________________
>>>> erlang-questions (at) erlang.org mailing list.
>>>> See http://www.erlang.org/faq.html
>>>> To unsubscribe; mailto:erlang-questions-unsubscribe@REDACTED
>>>>
>>>>
>>>
>>> ________________________________________________________________
>>> erlang-questions (at) erlang.org mailing list.
>>> See http://www.erlang.org/faq.html
>>> To unsubscribe; mailto:erlang-questions-unsubscribe@REDACTED
>>>
>>>
>>
>>
>
>
> ________________________________________________________________
> erlang-questions (at) erlang.org mailing list.
> See http://www.erlang.org/faq.html
> To unsubscribe; mailto:erlang-questions-unsubscribe@REDACTED
>


-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/