Home Ask Login Register

Developers Planet

Your answer is one click away!

Svitlana February 2016

Cassandra database model

I just switched to Cassandra and I have such items to model:

1.House - Here is its view which has 2.city, 3.zip and 4.property type

I will also need all cities view and I need all property types and zips for search form completions (not implemented yet).

To the best of my knowledge I should create 4 tables and make Primary keys ..."foreign keys", should not I?

I have also small request - like Harry Truman “GIVE me a one-handed economist,” demanded a frustrated American president. “All my economists say, ‘on the one hand...on the other'” :-).

I do not have enough experience with Cassandra to make a choice if I get "or you can do in this way, or that way", so just give me 1 best schema and I will implement it.

Thank you

Answers


Aaron February 2016

To the best of my knowledge I should create 4 tables and make Primary keys ..."foreign keys", should not I?

You definitely do not want to do that. First of all, foreign keys do not exist in Cassandra. Secondly, what you're talking about is modeling from a relational standpoint. With Cassandra, you don't want data for one query spread across multiple tables, because that could be spread across multiple nodes. And querying multiple nodes introduces more network time into the equation, which is slow.

In Cassandra, you want to take a query-based modeling approach. Sometimes that can mean one table for each query. Bearing that in mind, I am hearing that you need to query your properties two different ways:

  • By "house" (MLS?)
  • By city

Essentially, you should have a table to serve each of those queries:

CREATE TABLE housesbymls (
    mls text,
    city text,
    price bigint,
    propertytype text,
    state text,
    street text,
    year bigint,
    zip text,
    PRIMARY KEY (mls));

CREATE TABLE housesByCity (
  mls text,
  street text,
  city text,
  state text,
  zip text,
  propertyType text,
  price bigint,
  year bigint,
  PRIMARY KEY ((state,city),zip,mls));

After upserting some data, I can query by MLS:

aploetz@cqlsh:stackoverflow> SELECT * FROM housesByMLS WHERE mls='09110857';

 mls      | city     | price  | propertytype     | state | street                         | year | zip
----------+----------+--------+------------------+-------+--------------------------------+------+-------
 09110857 | Palatine | 104900 | Condominium Unit |    IL | 1025 North Serling Avenue, 211 | 1978 | 60067

(1 rows)

And I can query by state/city or state/city/zip:

aploetz@cqlsh:stackoverflow> SELECT * FROM housesByCity
    WHERE state='IL' AND city='Palatine';

or:

aploetz 

Post Status

Asked in February 2016
Viewed 2,560 times
Voted 12
Answered 1 times

Search




Leave an answer


Quote of the day: live life