One issue that will be encountered with this data model is the unbounded partition growth. Partition will continue to grow indefinitely over time and there will be a risk to hit the limit of 2 billions columns per partition.
Consider a composite partition key. Thanks, Gedeon On Wed, May 17, 2017 at 10:56 PM, Anthony Grasso <anthony.gra...@gmail.com> wrote: > Hi Nandan, > > If there is a requirement to answer a query "What are the changes to a > book made by a particular user?", then yes the schema you have proposed can > work. To obtain the list of updates for a book by a user from the > *book_title_by_user* table will require the partition key (*book_title*), > the first clustering key (*book_id*), and the second clustering key ( > *user_id*). > > i.e. SELECT * FROM book_title_by_user WHERE book_title=<BOOK_TITLE> AND > book_id=<BOOK_ID> AND user_id=<USER_ID>; > > If the book_id is unnecessary for answering the above query, it may be > worth changing the primary key ordering of the *book_title_by_user* table > to the following. > > CREATE TABLE book_title_by_user( > book_title text, > book_id uuid, > user_id uuid , > ts timeuuid, > PRIMARY KEY (book_title, user_id, book_id, ts) > ); > > This will then simplify the select statement to > > SELECT * FROM book_title_by_user WHERE book_title=<BOOK_TITLE> AND > user_id=<USER_ID>; > > Kind regards, > Anthony > > On 17 May 2017 at 13:05, @Nandan@ <nandanpriyadarshi...@gmail.com> wrote: > >> Hi Jon, >> >> We need to keep tracking of all updates like 'User' of our platform can >> check what changes made before. >> I am thinking in this way.. >> CREATE TABLE book_info ( >> book_id uuid, >> book_title text, >> author_name text, >> updated_at timestamp, >> PRIMARY KEY(book_id)); >> This table will contain details about all book with unique updated >> details. >> CREATE TABLE book_title_by_user( >> book_title text, >> book_id uuid, >> user_id uuid , >> ts timeuuid, >> primary key(book_title,book_id,user_id,ts)); >> This table wil contain details of multiple old updates of book which can >> be done by mulplie users like MANY TO MANY . >> >> What do you think on this? >> >> On Wed, May 17, 2017 at 9:44 AM, Jonathan Haddad <j...@jonhaddad.com> >> wrote: >> >>> I don't understand why you need to store the old value a second time. >>> If you know that the value went from A -> B -> C, just store the new value, >>> not the old. You can see that it changed from A->B->C without storing it >>> twice. >>> >>> On Tue, May 16, 2017 at 6:36 PM @Nandan@ <nandanpriyadarshi...@gmail.com> >>> wrote: >>> >>>> The requirement is to create DB in which we have to keep data of >>>> Updated values as well as which user update the particular book details and >>>> what they update. >>>> >>>> We are like to create a schema which store book info, as well as the >>>> history of the update, made based on book_title, author, publisher, price >>>> changed. >>>> Like we want to store what was old data and what new data updated.. and >>>> also want to check which user updated the relevant change. Because suppose >>>> if some changes not made correctly then they can check changes and revert >>>> based on old values. >>>> We are trying to make a USER based Schema. >>>> >>>> For example:- >>>> id:- 1 >>>> Name: - Harry Poter >>>> Author : - JK Rolling >>>> >>>> New Update Done by user_id 2:- >>>> id :- 1 >>>> Name:- Harry Pottor >>>> Author:- J.K. Rolls >>>> >>>> Update history also need to store as :- >>>> User_id :- 2 >>>> Old Author :- JK Rolling >>>> New Author :- J.K. Rolls >>>> >>>> So I need to update the details of Book which is done by UPSERT. But >>>> also I have to keep details like which user updated and what updated. >>>> >>>> >>>> One thing that helps define the schema is knowing what queries will be >>>> made to the database up front. >>>> Few queries that the database needs to answer. >>>> What are the current details of a book? >>>> What is the most recent update to a particular book? >>>> What are the updates that have been made to a particular book? >>>> What are the details for a particular update? >>>> >>>> >>>> Update frequently will be like Update will happen based on Title, name, >>>> Author, price , publisher like. So not very high frequently. >>>> >>>> Best Regards, >>>> Nandan >>>> >>> >> >
