Create a shelf relation from multiple books for different users

Question

I have the following relations:

Books(book_id, title, author),

Members(member_id, name, username, password) and

Librarian(librarian_id, name, username, password)

I want to create the relation(s) and schema for the following statements:

Create mini shelf from existing books (for Members and Librarian)
Members also can use the shelves that are created by the Librarian.

score 1 · Accepted Answer · answered May 19 '14 at 14:17

1

If I've understood the question correctly I would go with something like this:

Merge librarians in the member table and add IsLibrarian column to differentiate the two types of users

Members(member_id, name, username, password, islibrarian)

Shelf(shelf_id, caption, dateopened, owner, etc...)
ShelfDetails(id, shelf_id, bookid)

owner of the shelf is in members table (foreign key) and can be either a regular member or librarian.

What shelves a user can use is then up to your application. You can easily select shelves created by the user and those created by librarians using this schema.

P.S. It would also be nice when asking questions to show your effort put into solving this like showing what's your idea.

answered May 19 '14 at 14:17

OttO

419
4
9

So, Should I put `shelf_id` attribute into `Members` table as `Members(member_id, name,username, shelf_id,..)` to obtain a shelf for every user? Thanks – danishjo May 20 '14 at 08:55
If you put `shelf_id` in `Members` table then a Member can only have one shelf (which is OK if you want it this way) If you want members to have more then one shelf assigned then Branko Dimitrijevic's answer shows how it can be done with USER_SHELF table. This table hold information which member is assigned to which shelf, and each shelf can be used by 0,1 or many members. – OttO May 21 '14 at 20:41

score 0 · Answer 2 · edited May 23 '17 at 12:27

Looks like you need something similar to this (non-key fields omitted for brevity):

enter image description here

There is a differentiation between concepts of "creating a shelf" and "using a shelf":

The former is an 1:N relationship and is represented by the foreign key SHELF.USER_ID.
The latter is an M:N relationship and is represented by the junction table USER_SHELF.

The USER - MEMBER - LIBRARIAN hierarchy¹ can be represented either by a single table (possibly with some kind of "type flag") or using one of these strategies.

¹ The ER term is "category", aka. subclassing, subtyping, inheritance or generalization hierarchy.

Create a shelf relation from multiple books for different users

2 Answers2