CODEWITHSUNDEEP
mysqldatabase-design
Anuj Gakhar
Anuj Gakhar

Reputation: 691

database design with variable columns (mysql)

I am working on a problem where I am trying to figure out the best way to design the Db tables for this. Lets say I have multiple books and each book has variable number of pages in it...I need to track page views for every book

e.g. Book 1 = 10 pages , Book 2 = 20 pages, Book 3 and 100 pages and so on As the user views each page in a book, I need to log that information.

The motive is to come up with some aggregation numbers on the %views of each book. I should be able to say e.g. "70% people read Book 1 upto Page 7 and then drop off"

I envision it as 

book 1 page 1 1000 views
book 1 page 2 950 views
book 1 page 3 800 views
.....
...
book 1 page 10 50 views

and with this I can generate a drilldown report of sorts

however, with variable page numbers in each book, what is the best way to store this information?

any help would be much appreciated

Upvotes: 0

Views: 326

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You will need to add an extra table:

PagesViews:

  • BookId.
  • UserId.
  • PageNumber.
  • ViewDate.
  • ...

With a composite primary key(BookId, UserId, PageNumber).

Upvotes: 1

hkf
hkf

Reputation: 4520

Table with:

  • Book ID
  • Book Name

Another table:

  • Book ID
  • Page #
  • # of reads

Can also split the decomposition even further with a Book_ID/Page_ID/Page # table if you so desire.

Upvotes: 1

Related Questions