I have created an ER diagram for an online hostel management system. Please advise me if this diagram makes sense or needs improvement.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You should probably have a table "StaffHostel" which has StaffID/HostelID FKs. It should also have another field called "IsManager", which can be True/False. That is a better way to represent the Staff - Hostel relationship. Table would look like:
HostelID (FK) StaffID (FK) IsManager (True/False)
If this is for a Hostel, note that people often don't book Rooms, but book beds! There can be many beds in a single room (usually up to 8) with customers booking a bed in a particular room. That will complicate your design significantly.
You may want to put RoomPrice into the Room table and have a "DefaultRoomTypePrice" in the RoomType table, but that is getting into semantics. I just know from experience sometimes room can be the same type but different prices for certain reasons.
It is unclear how your Catalog table is linked to the Hostel, explain a bit more? What is a Catalog?
Otherwise looks pretty good, the only thing I can imagine you might want to do differently is to hand off the payment to another provider, storing Credit Card information is a very dangerous thing to do, leave it to the big boys who implement stringent security practices.
You can go for adding a table called
RoleType to store info about the role of employee by adding one more column in
Employee table as RoleId
Role table as with two column
here RoleId will be Pk for
RoleType table and and will be Fk in
Employee table, Now you can add all the roles like Manager, Caterer etc.
Don't got your
Catalog table,other wise looks fine.
On Credit card info, don't store it your data base you can integrate paypal or any other payment gateway