Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: neutral (nike.apache.org: local policy)
Date: Fri, 21 Dec 2012 12:49:55 +0100
From: David Mohl <dave@dave.cx>
To: user@cassandra.apache.org
Message-ID: <15D7F5AD4CB04DAA9E828F9F5367AF4B@dave.cx>
Subject: Correct way to design a cassandra database
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="50d44ce3_1eba5d23_4c3"

--50d44ce3_1eba5d23_4c3
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello!

I've recently started learning cassandra but still have troubles understanding the best way to design a cassandra database. 
I've posted my question already on stackoverflow but because this would very likely result in a discussion, it got closed. Orginal question here: http://stackoverflow.com/questions/13975868/correct-way-to-design-a-cassandra-database


Assuming you have 3 types of objects: User, Photo and Album. Obviously a photo belongs to a user and can be part of a album. For querying, assume we just want to order by "last goes first". Paging by 10 elements should be possible.

Would you go like every document has all the informations needed for a correct output. Something like this:

    -- User 
       | -- Name
       | -- ...
       | -- Photos
            | -- Photoname
            | -- Uploaded at

Or go a more relational way (while having a secondary index on the "belongs_to" columns:

    -- User (userid is the row key) 
       | -- Name
       | -- ...

    -- Photoid
       | -- belongs_to (userid)
       | -- belongs_to_album (albumid)
       | -- ...

    -- Albumid
       | -- belongs_to (userid)
       | -- ...

Another way that came in my mind would be kind of a mix:

    -- User 
       | -- Name
       | -- ...
       | -- Photoids (e.g. 1,2,3,4,5)
       | -- Albumids (e.g. 1,2,3,4,5)

    -- Photoid (photoid is the row key) 
       | -- Name
       | -- Uploaded at
       | -- ...

    -- Albumid (albumid is the row key) 
       | -- Name
       | -- Photoids (e.g. 1,2,3,4,5)
       | -- ...

When using a random partitioner, the last example would be (IMO) the way to go. I can query the user object (out of a session id or something) and would get all the row keys I need for fetching photo / album data. However this would result in veeery large columns. Another down point would be inconsistency and identification problems. A photo (or a album) could not be identified by the row itself. 
Example: If I fetch a photo with ID 3456, I don't know in which albums it is part nor which user owns it. Adding this kind of information would result in a fairly large stack of points I have to alter on creation / update.

The second example has all the information needed. However, if I want to fetch all photos that are part of album x, I have to query by a secondary index that COULD contain millions of entries over the whole cluster. And I guess I can forget the random partitioner on this example.

Am I thinking to relational? 
It'd be great to hear some other opinions on this topic

---
David


--50d44ce3_1eba5d23_4c3
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


                <div>
                    <div>Hello=21</div><div><br></div><div>I've recently =
started learning cassandra but still have troubles understanding the best=
 way to design a cassandra database.&nbsp;</div><div>I've posted my quest=
ion already on stackoverflow but because this would very likely result in=
 a discussion, it got closed. Orginal question here:&nbsp;<a href=3D=22ht=
tp://stackoverflow.com/questions/13975868/correct-way-to-design-a-cassand=
ra-database=22 style=3D=22color: rgb(0, 106, 227); =22>http://stackoverfl=
ow.com/questions/13975868/correct-way-to-design-a-cassandra-database</a><=
/div><div><br></div><div><div><br></div><div>Assuming you have 3 types of=
 objects: User, Photo and Album. Obviously a photo belongs to a user and =
can be part of a album. =46or querying, assume we just want to order by =22=
last goes first=22. Paging by 10 elements should be possible.</div><div><=
br></div><div>Would you go like every document has all the informations n=
eeded for a correct output. Something like this:</div><div><br></div><div=
>&nbsp; &nbsp; -- User&nbsp;</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- =
Name</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- ...</div><div>&nbsp; &nb=
sp; &nbsp; &nbsp;=7C -- Photos</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbs=
p; &nbsp; =7C -- Photoname</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &=
nbsp; =7C -- Uploaded at</div><div><br></div><div>Or go a more relational=
 way (while having a secondary index on the =22belongs=5Fto=22 columns:</=
div><div><br></div><div>&nbsp; &nbsp; -- User (userid is the row key)&nbs=
p;</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- Name</div><div>&nbsp; &nbs=
p; &nbsp; &nbsp;=7C -- ...</div><div><br></div><div>&nbsp; &nbsp; -- Phot=
oid</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- belongs=5Fto (userid)</di=
v><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- belongs=5Fto=5Falbum (albumid)</=
div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- ...</div><div><br></div><div>&=
nbsp; &nbsp; -- Albumid</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- belon=
gs=5Fto (userid)</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- ...</div><di=
v><br></div><div>Another way that came in my mind would be kind of a mix:=
</div><div><br></div><div>&nbsp; &nbsp; -- User&nbsp;</div><div>&nbsp; &n=
bsp; &nbsp; &nbsp;=7C -- Name</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C --=
 ...</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- Photoids (e.g. 1,2,3,4,5=
)</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- Albumids (e.g. 1,2,3,4,5)</=
div><div><br></div><div>&nbsp; &nbsp; -- Photoid (photoid is the row key)=
&nbsp;</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- Name</div><div>&nbsp; =
&nbsp; &nbsp; &nbsp;=7C -- Uploaded at</div><div>&nbsp; &nbsp; &nbsp; &nb=
sp;=7C -- ...</div><div><br></div><div>&nbsp; &nbsp; -- Albumid (albumid =
is the row key)&nbsp;</div><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- Name</d=
iv><div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- Photoids (e.g. 1,2,3,4,5)</div>=
<div>&nbsp; &nbsp; &nbsp; &nbsp;=7C -- ...</div><div><br></div><div>When =
using a random partitioner, the last example would be (IMO) the way to go=
. I can query the user object (out of a session id or something) and woul=
d get all the row keys I need for fetching photo / album data. However th=
is would result in veeery large columns. Another down point would be inco=
nsistency and identification problems. A photo (or a album) could not be =
identified by the row itself.&nbsp;</div></div><div>Example: If I fetch a=
 photo with ID 3456, I don't know in which albums it is part nor which us=
er owns it. Adding this kind of information would result in a fairly larg=
e stack of points I have to alter on creation / update.</div><div><br></d=
iv><div>The second example has all the information needed. However, if I =
want to fetch all photos that are part of album x, I have to query by a s=
econdary index that COULD contain millions of entries over the whole clus=
ter. And I guess I can forget the random partitioner on this example.</di=
v><div><br></div><div>Am I thinking to relational=3F&nbsp;</div><div>It'd=
 be great to hear some other opinions on this topic</div><div><br></div><=
/div><div><div>---</div><div>David</div><div><br></div></div>
            
--50d44ce3_1eba5d23_4c3--