Many to many dimension table Library Database

Question

I need to find which category of books were the most borrowed. So, I have the following UML diagram.

So when making the dimension tables and fact table I've come up up with the following:

But the Auth (dimension)association table doesn't seem right. Is there a mistake and how can I correct it? Edit: Probably Category and Publisher dimension tables shouldn't be there. They should be attached with book.

Answer 1

your dimensional model needs to be redesigned.

Fact table must be "Auth" with the following fields:

• Staff_ID
• Student_ID
• Book_ID
• Issued_Date
• Borrowed_Till

Then you need 4 dimension tables:

• Staff (contains Staff key, Staff attributes, Library attributes)
• Student (Student key, Student attributes)
• Book (Book key, Book attributes, Publisher, Category)
• Calendar (Date key, calendar attributes) you might need a second Calendar for Borrowed_Till.