DB design for inventory system

[raju143]

As of now there are 10 branches and it will expand in the future. Books are supplied to these branches and these books will be given to the students at each branch. The book a student gets depends upon the course he is undergoing. The customer wants the inventory list branch wise and at the same time he also wants log of when books were added and when books were given to students.

 

I am not able to think of a DB table design which suits the customers requirement. Any ideas?

[Daniel0]

It's a basic many-to-many relationship.

 

---

 

branches

branch_id, unsigned integer, primary key

* etc. *

 

students

student_id, unsigned integer, primary key

branch_id, unsigned integer, key

* etc. *

 

books

book_id, unsigned integer, primary key

* etc. *

 

student_book

student_id, unsigned integer, key

book_id, unsigned integer, key

created_at, datetime

 

---

 

If Joe has user ID 142 then you can get his books like this:

SELECT b.*, join.created_at AS book_given
 FROM books AS b
 INNER JOIN student_book AS join
   ON b.book_id = join.book_id
 WHERE join.student_id = 142;

[Barand]

I don't see "course" in that data model, and it's the course that dictates which books they get.

 

I'm thinking an entity relationship along these lines

 

[pre]

      course                        book  ------<  stockreceipt  >-----  branch

      |  |                        |  |                                  |

      |  +---< course_book >-------+  |                                  |

      |                                |                                  |

      |                                |                                  |

      ^                                ^                                  |

    student ----------------------<  issue  >-----------------------------+

 

[/pre]

[raju143]

Thanx Daniel0 & Barand for your response.

 

I am a newbie to database design and structures. I can understand basic SQL queries. The query which is mentioned here, the database structure and the many-to-many relationship are all new to me.

 

Can anyone suggest me a good resource which will help me to understand the above mentioned stuff and also which will help me to design and achieve complex database designs.

 

Thanks in advance.

[Barand]

http://www.phpfreaks.com/tutorial/data-joins-unions