Reg:- Data Modelling Concepts

["@Nandan@" <na...@gmail.com>]

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

Re: Reg:- Data Modelling Concepts

[Jonathan Haddad <jo...@jonhaddad.com>]

Sorry, I hit return a little early.  What you want is called "event
sourcing": https://martinfowler.com/eaaDev/EventSourcing.html

Think of it as time series applied to state (instead of mutable state)

CREATE TABLE book (
name text,
ts timeuuid,
author text,
primary key(bookid, ts)
);

for example, if you insert the record:

insert into book (name, ts, author) values ('jon talks data modeling',
now(), 'jon haddad');

and then you find out that my first name is actually jonathan:
insert into book (name, ts, author) values ('jon talks data modeling',
now(), 'jonathan haddad');

now you've got 2 records for book, with a full history of the changes.  The
last change has the current record.

Jon

On Tue, May 16, 2017 at 6:44 PM Jonathan Haddad <jo...@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@ <na...@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
>>
>

Re: Reg:- Data Modelling Concepts

[Gedeon Kamga <gk...@gmail.com>]

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 <an...@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@ <na...@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 <jo...@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@ <na...@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
>>>>
>>>
>>
>

Re: Reg:- Data Modelling Concepts

[Anthony Grasso <an...@gmail.com>]

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@ <na...@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 <jo...@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@ <na...@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
>>>
>>
>

Re: Reg:- Data Modelling Concepts

["@Nandan@" <na...@gmail.com>]

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 <jo...@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@ <na...@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
>>
>

Re: Reg:- Data Modelling Concepts

[Jonathan Haddad <jo...@jonhaddad.com>]

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@ <na...@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
>