Hi everyone, Can anyone please let me know if I am heading to an antiparttern or somethingelse bad?
How would you model the following ... ? I am migrating from MYSQL to Cassandra, I have a scenario in which need to store the content of "to be sent" transactional email messages that the customer will receive on events like : an order was created, an order was updated, an order was canceled,an order was shipped,an account was created, an account was confirmed, an account was locked and so on. On MYSQL there is table for email message "type", like: a table to store messages of "order-created”, a table to store messages of "order-updated" and so on. The messages are sent by a non-parallelized java worker, scheduled to run every X seconds, that push the messages to a service like Sendgrid/Mandrill/Mailjet. For better performance, easy to purge and overall code maintenance I am looking to have all message "types" on a single table/column family as following: CREATE TABLE communication.transactional_email ( objectid timeuuid, subject text, content text, fromname text, fromaddr text, toname text, toaddr text, wassent boolean, createdate timestamp, sentdate timestamp, type text, // example: order_created, order_canceled domain text, // exaple: hotmail.com. in case need to stop sending to a specific domain PRIMARY KEY (wassent, objectid) ); create index on toaddr create index on sentdate create index on domain create index on type The requirements are : 1) select * from transactional_email where was_sent = false and objectid < minTimeuuid(current timestamp) limit <number> (to get the messages that need to be sent) 2) update transactional_email set was_sent = true where objectid = <timeuuid> (to update the message right after it was sent) 3) select * from transactional_email where toaddr = <emailaddr> (to get all messages that were sent to a specific emailaddr) 4) select * from transactional_email where domain = <domain> (to get all messages that were sent to a specific domain) 5) delete from transactional_email where was_sent = true and objectid < minTimeuuid(a timestamp) (to do purge, delete all messages send before the last X days) 6) delete from transactional_email where toaddr = <emailaddr> (to be able to delete all messages when a user account is closed) Thanks IPVP
