CODEWITHSUNDEEP
databasedatabase-design
Tomi
Tomi

Reputation: 13

Database Design-struggling with relationships and FKs

I am building a website which requires a database. I want users to be able to

  1. Log into the website (so user details need to be preserved)
  2. Search for resources
  3. Search for e-books
  4. Add resources/ebooks to personal library

I have created four tables User, Resources, Ebooks, Library

User table consists of the following fields

User_ID
User_first_name
User_Last_name
Username
User_password (encrypted)
User_Email

Resources consist of the following fields

Resource_ID
Resource_Name
Resource_Category

Ebooks consist of the following fields

Ebook_ID
Ebook_name
Ebook_category

Library consists of the following fields

Library_ID
User_ID

I'm struggling with the relationships and FKs. I have created a relationship between the user and the library as 1 user will have 1 library however, I want users to be able to add as many books/resources as they want to their personal library so what would the relationship between the user and the resources/ebook be?

This may be a really easy solution but I'm new to databases so don't judge!

Thanks in advance.

Upvotes: 1

Views: 40

Answers (1)

Prasath Rajasekaran
Prasath Rajasekaran

Reputation: 375

From the question, I understand/assume that a resource/ebook can be in many libraries and a library can contain many resources/ebook (many-to-many)

Hence we can create the mapping table as follows:

ResourceLibraryMap
----------------
resource_id
library_id

EbookLibraryMap
-------------
ebook_id
library_id

By this way, we have created a many_to_many relationship between library-resources and library-ebooks. Hence, user will have many resources/ebooks through ResourceLibraryMap/EbookLibraryMap respectively

Upvotes: 1

Related Questions