Dear Wiki user, You have subscribed to a wiki page or wiki category on "Cassandra Wiki" for change notification.
The "ThomasBoose/EERD model components to Cassandra Column family's" page has been changed by ThomasBoose. http://wiki.apache.org/cassandra/ThomasBoose/EERD%20model%20components%20to%20Cassandra%20Column%20family%27s?action=diff&rev1=14&rev2=15 -------------------------------------------------- This page describes model tranformations from (E)ERD concepts into Cassandra ColumnFamily concepts. All input is welcome. == DBMS layer == - At several spots in this document you wil find suggestions to implement trivial DBMS functionality by hand. At this stage, I would suggest to programmers to implement at least 4 tiers when using Cassandra as a backend server. One would be the database layer by Cassandra itself, One would be a tier implementing DBMS rules, another for business rules finishing with an application tier. + At several spots in this document you will find suggestions to implement trivial DBMS functionality by hand. At this stage, I would suggest to programmers to implement at least 4 tiers when using Cassandra as a back-end server. One would be the database layer by Cassandra itself, one would be a tier implementing DBMS rules, another for business rules finishing with an application tier. - In this DBMS tier functions should be available for keeping data consistend based on datarules and it would throw exceptions when indexes are changed or orders are given to delete key's agains DBMS rules. + In this DBMS tier, functions should be available for keeping data consistent based on datarules and it would throw exceptions when indexes are changed or orders are given to delete keys against DBMS rules. - If this is not yet making sence, read on. + If this is not yet making sense, continue reading. === Indexing === - In order to add an index to a column, other then the ColumnFamily key, we should to create a second ColumnFamily. Every insert, which can be either an insert or update in Cassandra, on the original ColumnFamily we will update the corresponding index. + In order to add an index to a column, other than the ColumnFamily key, we should create a second ColumnFamily. Every insert, which can be either an insert or update in Cassandra, on the original ColumnFamily we will update the corresponding index. - Think of a ColumnFamily cf_Person (examples in Python using pycassa) + Think of a ColumnFamily cf_Person (examples in Python using Pycassa) {{{ cf_Person.insert('234', {'name':'Karel','City:'Haarlem'}) cfi_Person_City.insert ('Haarlem', {'234':''}) }}} - This way a hash will be created containing columns for every person's key that lives in a specific City. The ColumnFamily architecture of Cassandra can store a unlimited number of columns for each key. This meens that when deleting a person it's reference in the cfi_Person_City index should be removed first. When updating a person, maybe moving to anothor City, we have to remove the element from the cfi_Person_City first and then store it with the corresponding new City.'' '' + This way a hash will be created containing columns for every persons key that lives in a specific City. The ColumnFamily architecture of Cassandra can store an unlimited number of columns for each key. This means that when deleting a person, it's reference in the cfi_Person_City index should be removed first. When updating a person, maybe moving to another City, we have to remove the element from the cfi_Person_City first and then store it with the corresponding new City.'' '' === Deleting values === - Because of the way Cassandra clusters operate it is nearly impossible to delete values and know for sure the values are deleted on every node. If values would simply be deleted and afterwards a node turns up which still holds the given value it would replicate ths value back to the existing nodes. Read more about deleting values at: DistributedDeletes + Because of the way Cassandra clusters operate it is nearly impossible to delete values and know for sure the values are deleted on every node. If values would simply be deleted and afterwards a node turns up which still holds the given value, it would replicate this value back to the existing nodes. Read more about deleting values at: DistributedDeletes - Cassandra on the other hand is isanely fast at inserting and updating values. This is why I would advise any programmer trying to build DBMS logic to introduce a value that "meens " deleted and have you DBMS tier respond "Does not exist" in both cases, when values actualy do not exist or contain the "deleted" value. Cassandra makes no distinction between updates and inserts so updating from "deleted" and inserting can be achieved the same way. + Cassandra on the other hand is isanely fast at inserting and updating values. This is why I would advise any programmer trying to build DBMS logic to introduce a value that 'means' deleted and have you DBMS tier respond "Does not exist" in both cases, when values actually do not exist or contain the 'deleted' value. Cassandra makes no distinction between updates and inserts so updating from 'deleted' and inserting can be achieved the same way. == Relations == === 1 on 1 === - Typicly you'll find three kinds of 1 on 1 relations in a relational model. I will address them one at a time. + Typically you'll find three kinds of 1 on 1 relations in a relational model. I will address them one at a time. ==== Equal elements ==== - Sometimes all the elements are part of both collections on either side of the relationship. The reasons these collections are moddeled seperately are most often based on security issues or functional differences. One solution in a Cassandra database would be the same as you would implement such a relation in an RDBMS. Simply by sharing the same key in both ColumnFamily's. Inserting a key in one of these ColumnFamily's would insert the same in the other and vise versa. Updating an existing key in either ColumnFamily would not result in any change in the other. Deleting a key from one ColumnFamily will result in deleting the same key in the other family as well, providing this would be allowed. + Sometimes all the elements are part of both collections on either side of the relationship. The reasons these collections are modelled seperately are most often based on security issues or functional differences. One solution in a Cassandra database would be the same as you would implement such a relation in an RDBMS. Simply by sharing the same key in both ColumnFamily's. Inserting a key in one of these ColumnFamily's would insert the same in the other and vise versa. Updating an existing key in either ColumnFamily would not result in any change in the other. Deleting a key from one ColumnFamily will result in deleting the same key in the other family as well, providing this would be allowed. - ''I'm not sure to what detaillevel security rules can apply in a Cassandra database. At least I know that one can creat logins per cluster.'' + ''I'm not sure to what detaillevel security rules can apply in a Cassandra database. At least I know that one can create logins per cluster.'' If it is necessary to use different keys for both collections, sometimes it is not up to one designer to select both keys, although the number of element are equal and they are related one on one, in a relational model the designer gets to select either key to insert into the other collection with an unique and foreign key constraint. ''' {{http://boose.nl/images/oneononeequal.jpeg}} ''' - In Cassandra modeling you are forced to either croslink both key's, So you'd design both key's foreign in both ColumnFamily's. Or you create a third ColumnFamily in which you store both keys preceded by a token to which columfamily you are refering. Lets focus on the first option. Say we hand out phones to our employees and we agree that every employee will always have one phone. and phones that are not used are not stored in our database. The phone has a phonenumber as key where the employee has a social security number. In order to know which number to dial when looking for employee X and who is calling giving a specific phonenumber we need to store both keys foreign in both ColumnFamily's. + In Cassandra modeling you are forced to either croslink both keys, So you'd design both keys foreign in both ColumnFamily's. Or you create a third ColumnFamily in which you store both keys preceded by a token to which columfamily you are refering. Lets focus on the first option. Say we hand out phones to our employees and we agree that every employee will always have one phone. and phones that are not used are not stored in our database. The phone has a phonenumber as key where the employee has a social security number. In order to know which number to dial when looking for employee X and who is calling giving a specific phonenumber we need to store both keys foreign in both ColumnFamily's. ||||||||<style="text-align: center;">CF_Employee ''' ''' || ||<style="text-align: center;" |2>123-12-1234 ||name ||phone ||salary || ||John ||0555-123456 ||10.000 || @@ -115, +115 @@ === Many to Many === {{http://boose.nl/images/manytomany.jpeg}} - Lets look at a very basic part of an (E)ERD model. The power of chen ERD models is that a lot of implicit information can be left out. Models should be simplefied versions of their reallife counterparts. No DBMS, relational nor NoSQL, can by itself implement many to many relationships. In Relational systems we would create a new table that would represent the relationship. This table would consist of both the key's (foreign keys) of the adjaccent entities, being primairy togetter, supplemented with its own attributes. + Lets look at a very basic part of an (E)ERD model. The power of chen ERD models is that a lot of implicit information can be left out. Models should be simplefied versions of their reallife counterparts. No DBMS, relational nor NoSQL, can by itself implement many to many relationships. In Relational systems we would create a new table that would represent the relationship. This table would consist of both the keys (foreign keys) of the adjaccent entities, being primairy togetter, supplemented with its own attributes. As it is perfectly valid in a relational database to have a key value composed of several columns, in Cassandra there is only one key per ColumnFamily. I did already discuss the need to create seperate ColumnFamily's for one to many relationships given the fact that you can never tell for sure whether or not maybe in the future a new relation will popup to another entity sharing the same keyvalues. This means that we will need 5 ColumnFamily's to implement the model above:
