I am new to database designing...
I need to collect a list of books published on Amazon with some static attributes and other dynamic ones.
For the dynamic I need to store the date on which the change occurred.
Then I'll need to collect all info on each book in the actual date or in a range of dates, with all the eventual changes on the dynamic data.
For the static info I did a table:
Book (bookID (P), title, author, subtitle, author, type of product (e-book, pb, audiobook), isColor )
Dynamic info like these:
pages reviews + ratings per market internal ranking per market price (that changes also other parameters I need: royalty perceived and ads parameters)
So, I need another table to put alla data changing on a book per market on a specific date.
But here I thought 2 approaches and I don't know which is better...
1) create several tables for each dynamic info, linked with FK:
Page (actualDate, bookID FK, page) Review (actualDate, bookID FK, market, review) Rating (actualDate, bookID FK, market, ratings) Ranking (actualDate, bookID FK, market, ranking) Price (actualDate, bookID FK, market, price, royalty, ACOS)
Here I insert a row only if that specific data changed
2) collect all these info on a single table, apart Price like this:
BookData (actualDate, bookID FK, market, priceID FK, page, review, rating, ranking) Price (priceID, price, royalty, ACOS)
Here I must insert a row even if one data only changed
all info changed on a date:
date = Oct 24, BookID = 35, price = 13.90, royalty = 5.01, ACOS = 43, page = 245, review = 232, rating = 312, ranking = 23467
Page --> Oct 24, 35, 245 Review --> Oct 24, 35, "com", 232 Rating --> Oct 24, 35, "com", 312 Ranking --> Oct 24, 35, "com", 23467 Price --> Oct 24, 35, "com", 1390, 501, 43
BookData--> Oct 24, 35, "com", 16, 245, 232, 312, 23467 Price --> 16, 1390, 501, 43
So, my questions:
I have 4x3 more repetitions in 1): [24 Oct, 35, "com"] compared to 2) one time only
Maybe could I create another table to create a rowID for a specific book+market? Like:
BookMarketDate (bmdID, bookID, actualDate, market ) BookMarketDate --> 7, Oct 24, 35, "com"
Then I could refer to this in tables above like:
Ranking ( bmdID FK, ranking) Ranking --> 7, 23467
Or is a unnecessary complication?
B) In 2) if I have only one info changed, like ranking, I must insert a new row with null values on other fields? Like:
BookData--> Oct 25, 35, "com", null, null, null, 23467
but when I need to retrieve all data on a specific date for bookID 35, how would I do ?
It seems simpler to query with 1) ...
C) In 1) could I put review and ratings in the same table because they are similar data...? Like:
Review (actualDate, bookID FK, market, review, rating)
But this way the query could be more complex to get actual review and rating situation because if I have review data, I have also ratings, instead if I have ratings I could not have review data, hence null
So, on 25 Oct I could have review=232 and rating = 321 but info splitted in 2 date rows:
Review --> Oct 24, 35, "com", 232,312 Review --> Oct 25, 35, "com", null, 321
Which DB structure do you suggest for this case?