2

I am working on designing a database for conference room scheduler application. And we have rooms that can be merged to create a bigger one. for example, Room A can be merged to Room B to hold 64 people. I have attached my Database design below I couldn't figure out how to accommodate this feature at the back end. database level. cRoom database design diagram

And also, I would like my database to accommodate the rooms information that could be merged. lkRooms table only list single rooms and their capacity. there are rooms that can be merged specifically with one adjacent to them (roomA + room B) and also I have quad rooms ( RoomC + RoomD + RoomE + RoomF). these rooms can be used as a single rooms and can be merged to create one big room. I do I make my database accommodate this? I want the application to get room information from DB. if what I want to say make any sense at all

Community
  • 1
  • 1
zazzu
  • 37
  • 9
  • *Room A can be merged to Room B* which means? Besides your mergedRooms table is not normalized – juergen d Dec 22 '17 at 16:39
  • On the application when users book a room based on capacity there are rooms that can be merged. – zazzu Dec 22 '17 at 16:41
  • yes my mergedRooms table is out of place. It is not the right approach it is where I need help. – zazzu Dec 22 '17 at 16:42

2 Answers2

0

What I would do is have the booking table reference an intermediate table, let's say bookingRoom, by fk bookingRoomID. The bookingroom record would then reference lkRoom by RoomID. bookingRoom would have multiple records for a given bookingRoomID for a "merged" room. Otherwise, for a single room booking, there would just be a single record in bookingRoom.

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • Yes true that is one approach. And also , i would like my database to accommodate the rooms information that could be merged. lkRooms table only list single rooms and their capacity. there are rooms that can be merged specifically with one adjacent to them (roomA + room B) and also I have quad rooms ( RoomC + RoomD + RoomE + RoomF). these rooms can be used as a single rooms and can be merged to create one big room. I do I make my database accommodate this? I want the application to get room information from DB. if what I want to say make any sense at all – zazzu Dec 22 '17 at 18:24
0

I think you need a 'partition' concept that allows rooms to merge, so instead of storing the largest room possible, you store the smallest, and people can reserve a partition to form the larger room whose capacity equals the sum of the smaller rooms. Then you need a roomPartition relationship. So if someone wants to book a merged room, they also book the partition.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • would you please explain more :) can you see my database diagram pic I included? the lkRooms table hold the smallest partition possible. but my difficulty is how to include which room partition goes with which one. for example roomA can be merged only room B. – zazzu Dec 22 '17 at 18:45
  • @Zazzu in that case, you might want to create a "mergeMap" that has all the merged room configurations possible. If a merged room is needed for a booking, you could then select one from the mergeMap. – digital.aaron Dec 22 '17 at 18:59
  • I think the easiest solution is to treat the partitions as if they are always in place and allow the requester to remove them. So drop your mergedRoom table entirely and treat a room with one partition like two different rooms, 1A and 1B. A requester intending to remove the partition reserves both smaller rooms. OTOH, if you need to represent which rooms can be merged, you'd need to store a mergedRoomID (mergedRoomID for rooms 1A and 1B is 1) and a 1-to-many relation with mergedRoom (1,1A) and (1,1B). – Beth Dec 23 '17 at 21:43