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