Availability Calendar DB Table Structure

Factual Questions
1

I am looking for the best way to set up a DB to hold availability of resources. Let’s say it is conference rooms available on a per day basis. What I am thinking is that each room would be a row in a table and the columns would be each day. If it was available, there would be a 0. If not, there would be a reservation ID#. That ID# would be linked to a table with the reservation information, i.e. name of the person who reserved it, their contact info, purpose, etc.

Is that how I should be setting it up? If so, what query would I run to return the dates of a reservation?

2

You’re plan would entail a table with arbitrarily many rows (representing the various rooms, if there are lots and lots of them) and arbitrarily many columns (one for each date, and I suppose you would keep adding new columns as you open the reservation books for each new month or whatever). A general rule for DB design is that you should have a fairly fixed number of columns and not just keep adding columns like that. It would also be difficult to search a database designed like that, at least with typical modern relational DB design.

Better still:

(1) Have one table that lists every existing room (or other schedulable resources maybe). The first column is an ID number (the primary key) that identifies that room. Other fields can have additional information describing the room, like what building it is in, what floor, the floor size, how many people it can accommodate, or whatever. Each room is a separate ROW in the table. You can add or delete rows as needed.

(2) Have another table listing every DATE that the facilities are potentially available. You might be able to get by without this table, since in concept its rows (simply a list of days) could be generated on the fly anyway.

(3) Have a third table - and this is the important one - listing what rooms are available on what days. This has one column containing the ID linking to the room in question, and one column with the ID linking to the date in question (or, if you don’t have a date table, it would be just the date). Additional fields might contain the reservation information. (We’re keeping it simple – assuming just one reservation per room per day. If a room is reserved in hour-blocks and can have several reservations each day, that’s messier.)

The two columns (room ID linking to the room table, and date ID linking to the date table or simply the date itself) together form the primary key for this table.

This table has one ROW for each combination of ROOM X DATE for which there is relevant information. For each combination of ROOM X DATE that isn’t reserved at all, there is no row. So your algorithm would be like this:

(a) Request comes in to reserve ROOM123 for 2012-07-15.
(b) Look for the record with ROOM123 2012-07-15 in this table.
© If you find a record with this combination of data, it means THAT room is reserved on THAT date. Additional fields may be present in this record with other information that you considered relevant.
(d) If you don’t find such a record, then the room is not reserved on that day. In this case, you may make the reservation, and add a new record to the table, with ROOM123 2012-07-15, along with filling any other relevant fields.
(e) If a request comes to cancel an existing reservation, look it up similarly. If found, delete the record. (Or keep a record somewhere else that a reservation was made and later canceled, if you need to keep that kind of history.) If not found, respond that you can’t delete the reservation because there is no such reservation.

ETA: BTW, I suppose I should mention that this is a VERY STANDARD way to set up tables with this kind of information. I’ll add another post, just for illustration, showing a similar problem with a similar solution.

3

Here’s another real-life example of a similar solution to a similar problem.

A company has a chain of retail department stores, with many stores in many cities. At the corporate headquarters, there is an inventory database tracking all the items that the stores carry, and their prices and quantities on hand.

BUT you have these twists:
(a) The same item could have different prices at different stores.
(b) You want to track the quantity at each store.

So you need some kind of big matrix, listing all the stores across the top, and all the items down the side. (Picture a spreadsheet looking like this.) In each cell, for a certain item at a certain store, you could put the price of THAT item at THAT store. You might have a separate table, with similar rows and columns, for the quantities on hand. Each cell would have the QoH for a particular item at a particular store.

Here’s the standard way to set up a relational database for this:

(a) One table listing all the stores, with one ROW for each store. It has a primary key (some code number identifying the store), and other fields with the address, city, state, zip, phone, and other details about the store.

(b) Another table listing all the items you carry. It has a primary key (customarily called the PLU Number) giving a code number for the item. (These days, that UPC bar code on most products is commonly used for this.) Other fields contain the description of the item, and bunches of other data.

© Your matrix table (commonly called a “cross table”) has a one ROW for EACH item at EACH store. If store XXX carries item YYY, then there is a row for that. If store WWW does NOT carry item VVV, then there is NO row for that. So each row represents ONE item at ONE store. Additional fields in this table would give the price for THAT item at THAT store, and the QoH for THAT item at THAT store. Each day, as sales data are reported from the stores to Corporate Headquarters, the quantities would be adjusted. And each day, as prices are changed at various stores, the table would be updated to show that, and the data downloaded to the cash registers at the stores.

ETA: Next step for you: Pick up ANY basic database or SQL teach-yourself book (that DOESN’T have “Dummies” or “Idiots” in the title) and read about “Many to many relationships” or “cross tables” for more on this topic.

4

Thanks a bunch. This is really helpful.

Would it be best to store the date as a #, i.e. 06272012?

5

Most databases have an explicit data-type for storing a date, so you don’t get to choose exactly how a date is represented internally. Just declare the date field to be of type DATE (or whatever it’s called in your system), and store the date there. The correct way to write a date in your queries or update commands would depend on the system you use.

Are you using a relational database management system (RDMBS) like Microsoft SQL Server or MySQL or PostgreSQL or similar? The suggestions I gave above are intended for systems like those.

Grabbing a SQL book I have laying handy, here are some examples:
In Microsoft Access queries, dates are written like #2001-01-01# (that’s year-month-day, I think).
In Microsoft SQL Server, dates are written like ‘2001-01-01’ (with single quotes, year-month-day, I think).
This book also shows examples of dates written with the keyword DATE, but I don’t see immediately what dialect of SQL that is. Example:
. . . WHERE pubdate >= DATE ‘2001-01-01’ ;

If you are storing dates in a text field (or an integer number) for some reason (which seems silly, unless you have some really good reason), you should definitely use YYYYMMDD format, since that will let you sort the data into order by date easily.

Maybe I should have made it clearer: When I wrote examples like ROOM123 2012-07-15 above, the room ID and data are separate fields, not combined into one field.

6

I just wanted to say that Senegoid is correct on basic database design philosophy. It sounds like you were making the common mistake of trying to turn a database into a spreadsheet when they are vastly different things theoretically and practically speaking. The output the database generates can end up being whatever you want including an actual spreadsheet that displays the results like you first described but the database design itself needs to follow the relational database model for it to work well at all.