raju143 Posted August 1, 2008 Share Posted August 1, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/117718-db-design-for-inventory-system/ Share on other sites More sharing options...
Daniel0 Posted August 1, 2008 Share Posted August 1, 2008 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; Quote Link to comment https://forums.phpfreaks.com/topic/117718-db-design-for-inventory-system/#findComment-605640 Share on other sites More sharing options...
Barand Posted August 2, 2008 Share Posted August 2, 2008 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] Quote Link to comment https://forums.phpfreaks.com/topic/117718-db-design-for-inventory-system/#findComment-605814 Share on other sites More sharing options...
raju143 Posted August 2, 2008 Author Share Posted August 2, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/117718-db-design-for-inventory-system/#findComment-605944 Share on other sites More sharing options...
Barand Posted August 2, 2008 Share Posted August 2, 2008 http://www.phpfreaks.com/tutorial/data-joins-unions Quote Link to comment https://forums.phpfreaks.com/topic/117718-db-design-for-inventory-system/#findComment-606035 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.