please help on SQLite DB structure: one or multiple tables

Fra Se 1 Reputation point
2022-10-29T14:41:31.867+00:00

Hi all,
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

i.e.
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
1)

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  

2)

BookData-->     Oct 24,     35,         "com",  16,         245,     232,   312,   23467  
Price   -->     16,         1390,       501,    43  

So, my questions:
A) repetitions:
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?
TIA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,045 questions
0 comments No comments
{count} votes