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 (

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

Kind regards,

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

Reply via email to