3.3 Database Model

[Jobieskii]

Conceptual db model

What do you think?

[do-ko]

physicalDatabseModel.pdf

[abinba]

Looks good and I don't think we should change anything for tomorrow.

Those are my thoughts on how we should implement this in the future and this should come in hand if I leave it here:

Should be added:

• UserSettings table or enhance Account table with settings options.

• Reviews table (we should be able to browse through user reviews)

• Add Audiobook image_cover column to display the image cover.

• Add listened_times (not sure about the name though) to Audiobook table for getting the most popular audiobooks.

Optimization:

• Audiobook should have a rating column, it is updated every time new rating is inserted -> in that way we wouldn't have to calculate the rating every time it's accessed

• Add finished flag to Listening table, in that way we know that the Audiobook has been finished listening.

Suggestions (let me know what you think of those use cases):

• Author should be a separate table -> we should enable going to Author so that we can see all audiobooks by the author. Also, view all the authors.

• We should be able to hide certain audiobook on demand (if publisher asks for it, for example). Add is_active to Audiobook table.

[Jobieskii]

[do-ko]

physical database model.pdf

[do-ko]

[Jobieskii]

I'm pretty sure in the physical model there shouldn't be plurality in relations. A foreign key points to one primary key (a table) or none – which would be denoted as FK NULL i guess.

He also did something like that to fully make it clear which connection is which.

+------+   A.b_id = B.id    +------+
|   A  | -----------------> |   B  |
+------+                    +------+

The arrow head is good but I don't think the more verbose constraint above the arrow makes it any more clear with proper naming (client_id is obviously pointing to client_id, right?)

[Experrior]

There's another thing, as I see in the User Interface Design, we have a notion of "favourite audiobooks" which should also be stored in database, we need to add it to both models. Also agree with previous point:

+------+    A.b_id = B.id      +------+
|   A  |   ----------------->  |   B  |
+------+                       +------+ 

but we can change it after tomorrow presentation, if you don't have time today.

[do-ko]

• added relationship descriptions (A.a_id=B.b_id) where the keys have different names.
• revoked multiple relations between tables (I think you meant chapter-option relations right?)
• added favorite audiobooks table to database model Please confirm if that is all there was to change :>

[do-ko]

Updated pdf and word: physical database model.pdf physical database model.docx

[Jobieskii]

Ok a couple comments:

• Matuesz is sort of right. The favorite in the UI was meant as just add to the shelf (which in the schema is synonymous with create a listening of this book for this user). If we do want a second layer of the library (books outside -> books added -> books favored) it should be just a boolean attribute of the listening.

• The chapter options was correct. I was specifically talking about removing these:

  and adding these if possible:

[Jobieskii]

updated conceptual model

[do-ko]

physical database model.pdf