Table design

[eacollie]

I have a database to schedule room reservations.

I have four tables:
tblEventReservation (holds information about event)
tblRoomReservation (hold informatio about room reserved)
tblRoom (holds information about rooms)
tblRoomNumbers (holds information about rooms that are in wings)

tblRoomReservation has foreign key to ID of tblEventReservation
tblRoom has foreign key to tblRoomReservation
tblRoomNumbers has foreign key to tblRoom

Some wings of the facility have multiple rooms (this information is kept in tblRoomNumbers)

Is this the best way to set up the tables?

Thanks!

[orange]

You may get some insight from models here
http://www.databaseanswers.org/data_...ons_dezign.htm

[jzwp11]

Assuming that an event might need several rooms and that a room can be reserved for many events (different dates of course), then that describes a many-to-many relationship between the events and the rooms.


tblEvent
-pkEventID primary key, autonumber
-txtEvent


tblRooms
-pkRoomID primary key, autonumber
-RoomNo

tblEventRooms (holds room(s) reserved for an event)
-pkEventRoomID primary key, autonumber
-fkEventID foreign key to tblEvent
-fkRoomID foreign key to tblRooms


I would have a table to hold the wing info

tblWings
-pkWingID primary key, autonumber
-txtWingName

Assuming that a room can only belong to 1 wing and a wing can have many rooms, you would just need a field in tblRooms to designate the wing to which it belongs

tblRooms
-pkRoomID primary key, autonumber
-RoomNo
-fkWingID foreign key to tblWings

[orange]

Here is another "reservation" model specific to events
http://www.databaseanswers.org/data_...ions/index.htm

None of these models may be exactly what you need. However, pieces of these models will probably approximate your set up. You can use pieces that "fit" and you can customize or add new entities and relationships as necessary to achieve a model that "matches" your situation.

If you start with a model and some sample conditions, you can test your model before creating tables. Once you create tables, you become reluctant to change, regardless of what the business rules may be telling you.

Define test conditions and business rules/facts - then test the model. Thoroughly review any discrepancies. Either the model is wrong, or the test material is wrong (possible both are wrong). Adjust "the culprit" as necessary and repeat, and repeat until no discrepancies are found. Now, do your tables.

[eacollie]

Thanks to all!

Unfortunately, I started with one model and then had to change because of user preference.

I think, based on your comments, I have it set up correctly. Thanks again!

[orange]

I don't mean to be picky, but often we start with a model. Then, as things get clearer, or as more and more questions get answered/clarified, we adjust the model to better represent the business.

This happens a lot, and is a good thing. If we all started out with the perfect model..... pretty boring (and not realistic).

When a model is adjusted, it shows we are all learning.

good luck.