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 >>> >> >
