Cassandra database model -

i switched cassandra , have such items model:

1.house - here view has 2.city, 3.zip , 4.property type

i need all cities view , need property types , zips search form completions (not implemented yet).

to best of knowledge should create 4 tables , make primary keys ..."foreign keys", should not i?

i have small request - harry truman “give me one-handed economist,” demanded frustrated american president. “all economists say, ‘on 1 hand...on other'” :-).

i not have enough experience cassandra make choice if "or can in way, or way", give me 1 best schema , implement it.

thank you

to best of knowledge should create 4 tables , make primary keys ..."foreign keys", should not i?

you not want that. first of all, foreign keys not exist in cassandra. secondly, you're talking modeling relational standpoint. cassandra, don't want data 1 query spread across multiple tables, because spread across multiple nodes. , querying multiple nodes introduces more network time equation, slow.

in cassandra, want take query-based modeling approach. can mean 1 table each query. bearing in mind, hearing need query properties 2 different ways:

• by "house" (mls?)
• by city

essentially, should have table serve each of 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 data, can query mls:

aploetz@cqlsh:stackoverflow> select * housesbymls 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 can query state/city or state/city/zip:

aploetz@cqlsh:stackoverflow> select * housesbycity     state='il' , city='palatine'; 

or:

aploetz@cqlsh:stackoverflow> select * housesbycity     state='il' , city='palatine' , zip='60067'; 

both of return:

 state | city     | zip   | mls      | price  | propertytype     | street                         | year -------+----------+-------+----------+--------+------------------+--------------------------------+------     il | palatine | 60067 | 09110857 | 104900 | condominium unit | 1025 north serling avenue, 211 | 1978  (1 rows) 

the idea behind primary key structure on one, state , city make partitioning key (which helps cassandra figure out put row in cluster) both required. then, cities can have multiple zip codes, can focus query on that. primary keys in cassandra unique, put mls on end ensure uniqueness.