question

FraSe-7351 avatar image
0 Votes"
FraSe-7351 asked Cathyji-msft edited

please help on SQLite DB structure: one or multiple tables

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















dotnet-sqlite
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers