cassandra-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Ellis <>
Subject Re: CQL support for compound columns
Date Sat, 31 Dec 2011 19:12:31 GMT
On Fri, Dec 30, 2011 at 12:30 PM, Eric Evans <> wrote:
>> CREATE TABLE timeline (
>>    user_id int,
>>    posted_at uuid,
>>    body string,
>>    posted_by string,
>>    PRIMARY KEY(user_id, posted_at, posted_by),
>>    VALUE(body)
>> );
> I think the value declaration also helps in that it's one more thing
> that provides cues as to the data model it creates (more expressive).
> But this got me thinking, why not introduce something special for the
> composite name as well?  That way the PRIMARY KEY syntax (which comes
> preloaded with meaning and expectations) could be kept more SQLish,
> and the whole thing looks more like an extension to the language as
> opposed to a modification.
> Say:
> CREATE TABLE timeline (
>  user_id int PRIMARY KEY,
>  posted_at uuid,
>  body text,
>  posted_by text,
>  COMPOSITE_NAME(posted_at, posted_by),
> )

I went back and forth on this mentally, but I come down as -0 on CN
instead of PK.  For two reasons:

First, the composite PRIMARY KEY is a better description of what you
can actually do with the data.  In a relational model, a PK of user_id
means there is only one (user_id, posted_at, body, posted_by) row with
a given user_id.  Which is not the case here.  PK = (row key +
composite components) captures exactly what is "immutable and unique"
in a given object, so it's actually exactly what it's meant for and
not an abuse at all.  (It even fits nicely with the "queries involving
the PK are always indexed" assumption that isn't required by the SQL
standard but every other database does anyway because it makes the
most sense.)

The only place where we do violence to relational expectations by
using PK this way is that "insert should raise an error if there's
already a row with that PK" (instead of updating that row the way we
do).  But, this is already a quirk we inflict on the use of PK, and I
don't think it's a big deal.

Second, it feels like this bleeds too much of the implementation into
the DDL.  What if we move to a storage engine like CASSANDRA-678 and
we represent this as more like the traditional relational rows than
composite columns?  Unlikely, I know, but the spirit of SQL is to
specify what you want to do and let the engine figure out how to do it
most efficiently.

Following the second line of reasoning made me realize that using
TRANSPOSED WITH <options> for things besides the PRIMARY KEY
definition [i.e., the COMPOSITE VALUE option] has the advantage of
emphasizing that it's a non-standard option that allows the
implementation to bleed through into the DDL.  But, I can see the
advantage in regularity of having COMPOSITE VALUE adjacent to the PK
definition as well.  So I'm fine either way, but I wanted to point
that out.

Jonathan Ellis
Project Chair, Apache Cassandra
co-founder of DataStax, the source for professional Cassandra support

View raw message