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=9&rev2=10 -------------------------------------------------- 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: - || + CF_Order is a straight forward ColumnFamily, Modeled after the design + ||||||||<style="text-align: center;">CF_Order ''' ''' || ||<style="text-align: center;" |2>1234 ||order_date ||order_discount ||customer_id || ||20100808 ||0.4 ||1234 || ||<style="text-align: center;" |2>4321 ||customer_id ||order_date || || - ||1234 ||20100802 || || + ||3451 ||20100802 || || + ||<style="text-align: center;" |2>1354 ||order_discount ||order_date || customer_id|| + ||3 ||20100802 || 3451|| - || + + + The same for CF_Product + - ||||||||<style="text-align: center;">CF_product ''' ''' || + ||||||||<style="text-align: center;">CF_Product ''' ''' || - ||<style="text-align: center;" |2>DSK_SGT_5GB_7200 ||description || image||list_price || + ||<style="text-align: center;" |2>DSK_SGT_5GB_7200 ||description ||image ||list_price || ||5 GB Seagate harddisk || ||130 || ||<style="text-align: center;" |2>KBD_LGT_WRL_350 ||description ||image ||list_price || - ||Wireless keyboard || ||75|| + ||Wireless keyboard || ||75 || + + + + As we cannot itterate throu a ColumnFamily based on any attribute except its key and we wan't to use a solution to relations that is generic we create a seperate ColumnFamily for each one to many relationship. As we know that a many to many relationship is typicly solved by introducing 2 one to many relationships to a newly created ColumnFamily. So there will be one ColumnFamily to store which orders a product can be found on. + + ||||||||<style="text-align: center;">CF_Product_Order ''' ''' || + ||<style="text-align: center;" |2>DSK_SGT_5GB_7200 ||1234 ||4321 || 1354|| + || || || || + ||<style="text-align: center;" |2>KBD_LGT_WRL_350 ||4321 || || || + || || || || + + + + + And we'll create one ColumnFamily that stores which products can be found on each order. We can see that this solution tends to invite anomalies. product that have 3 order columns but no reference to the product in the CF_Order_Product ColumnFamily. It up to the programmers of the DBMS tier to make sure genic code is available to keep the ColumnFamily's consistent al of the time. + + ||||||||<style="text-align: center;">CF_Order_Product|| + ||<style="text-align: center;" |2>1234 ||DSK_SGT_5GB_7200 || || || + || || || || + ||<style="text-align: center;" |2>4321 ||KBD_LGT_WRL_350 ||DSK_SGT_5GB_7200 || || + || || || || + ||<style="text-align: center;" |2>1354 ||DSK_SGT_5GB_7200 || || || + || || || || + + + ''' '''Last but not least we'll introduce a ColumnFamily to store the attributes connected to the relationship between Product and Order. As mentioned it is not possible to create a key containing 2 seperate values. So we'll have to introduce a new value that conatenates both values using a strikt format. These formats should be part of a design if implementing a (E)ERD in Cassandra. + + ||||||||<style="text-align: center;">CF_order_line''' ''' || + ||<style="text-align: center;" |2>1234_DSK_SGT_5GB_7200 ||number ||delivery_date || || + ||5 || 20101215|| || + ||<style="text-align: center;" |2>4321_KBD_LGT_WRL_350 ||product_discount ||number ||delivery_date || + ||0.3 || 1||20100901 || + ||<style="text-align: center;" |2>4321_DSK_SGT_5GB_7200 ||product_discount ||number ||delivery_date || + ||5 ||2||20100901 || + ||<style="text-align: center;" |2>1354_DSK_SGT_5GB_7200 ||product_discount ||number ||delivery_date || + ||5 ||8||20100901 || + + +
