I need help with the design of my schema. I'm almost new at this and I'm not sure whether my relationships between the tables ROOM, GUEST and RESERVATION below are correct.

Also, I have problems figuring out what to do regarding the possibility of ROOM CHANGE: An example:

Michael Keaton has one reservation in which he took two rooms. The first time, he didn't like the room so the hotel moved him to another room, and the second time there was a plumbing problem in the same room so they moved him again. My database needs to keep record about every room change.

Here's the entity relationship diagram:

ER diagram

1 Answer 1


Sounds like you need a history table for room changes that should be made most likely through the app layer. I know it's tempting to use built in triggers to just have it log it, but a simple app layer should do the trick.

If you're really concerned about that and you have the CDC option feel free to use that, but I would go with a transaction that logs the current record elsewhere, updates it, and commits with rollback error handling if it fails and a nice error for the end user to take some other step for the customer in the mean time while this error is looked at.

Edit: Updating based on question. To do this, you could write a stored procedure that you call from your app which:

-begins a transaction

-copies the current hotel number and occupant id along with a timestamp to a 'getRoomChangeHistory' type stored procedure

-then issue a 'updateRoomChange' type stored procedure so the new value is updated

-Finally COMMIT the transaction. Add Add error handling for failures. If you want a solid auditable way of doing it, this is it. If you want easy, it won't be dependable. For that, why not just save it as a document store somewhere else then if you don't care about ACID compliance?

Here's some more on the matter for SQL 2012 and above using TRY CATCH from Books OnLine to achieve this: https://msdn.microsoft.com/en-us/library/ms175976.aspx

  • Well, so sorry to say this...because i am thankfull for your help...but i didn't understand one word about what should i do...I need the simplest way to solve this... Nov 9, 2015 at 20:24
  • @user2923389 updated answer. Nov 9, 2015 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.