How to build an availability checker for holiday?

Hi, i have just started learning coldfusion and am extremely excited about the new possibilities data integration brings to a website!
My first project is to build a site for holiday villas which i would like to include a database for availability.
My initial idea was to build a database with 365 columns, one for each day of the year, then the availability checker would be a database query to see what values were in the columns (type thing!)
I would like to be able to add booking details to each date when they happen so as an administrator i can see whats happening on certain dates. Would this be a relational database, one for bookings and one for the dates?
Im using coldfusion on iis5 and microsoft access databases.
I have completed some tutorials and understand setting up the site with a remote folder and testing server and can set up data sources.

Can anyone help me figure out how to stucture the databases to handle this functionality, is the way im thinking somewhere right or is there a much better way??? Please help i cant wait to learn!

You definitely *don't* want to build a database with 365 columns just to handle lookups for dates. That's an extremely denormalized (and inefficient) design. Think of how many empty cells you'd have in your database.

What about something like this (table name at top then the column names):

Properties:
propertyID (primary key)
propertyName
propertyAddress

Bookings:
bookingID (primary key)
propertyID (foreign key)
bookingDate


Then you can look up dates or property id's in the Bookings table to find out what properties are booked on a certain date, or find out what dates a specific property has been booked for, etc. If you want to get fancy you can make the bookingID, propertyID and bookingDate a unique key so that you'll never have 2 bookings for the same property at the same time, but that's just one way to enforce that business rule.

hope that helps.

Thats a great help, thankyou!
That sounds like a much better idea, would i than be able to add other columns to the 'properties' table like the location, accomodation, facilities etc.
Also would the 'bookings' table include columns for who made the booking, their contact details and credit card details for payment.
I am interested to understand business rules as as you mentioned it will be important to ensure that there are no dates which are the same, can you point me in the direction of any tutorials which can help me learn how to write these rules.

Thanks again for your guidance its really appreciated.

stu

No, your bookings table would not hold the customer info, it would again contain a foreign key to relate this to a record in another table (just like the propertyID relates the booking to the property), so you might have:

Properties:
propertyID (primary key)
propertyName
propertyAddress

Customers:
customerID (primary key)
customerName
customerAddress

Bookings:
bookingID (primary key)
propertyID (foreign key)
customerID (foreign key)
bookingDate

It's generally a VERY bad idea to store credit card numbers in a database unless you are someone like Amazon.com who can spend millions of dollars ensuring that it is secure. If you want to do online credit card bookings, just do the credit card validation and charging when they book it, and store the approval codes, etc, but don't store the actual CC number.

Also, the Access documentation should have information on unique keys and how to create them. On the whole, if you are planning to develop database-driven web sites, a thorough understanding of relational database design and operation is critical. I'd strongly recommend you buy a few good books on databases and ColdFusion to enhance your skills.

your a star! nice one, i have been reading all day, so much to learn! i have been learning about cftransaction today, came across an article and realised how important it is to have built an application which can cope with volume of visitors in case it actually takes off sucessfully.
Your information is a great help and moral boost. I am studying part time for an A+ in england but need desparately as you say to study access inside out.
Although i read that mysql was a more solid business model for databases? I would be greatfull for your input as as i havent learned any databases (in detail) if i was going to study hard and learn one inside out, which would be the best to learn? I have heard several mentioned such as oracle, ibmdb2, sybase, informix, mysql, wot u rekon?
ill cross the credit card bit when i get that far, your input has got me started and i can make a start on the username login part and plan how its gonna be built.
one last thing, i get ya with the separate booking tamble, you werent totally clear wether its ok to add other colums to the other tables you suggersted, can the user table contain info about them, name address contact etc, and an account level if i have tiers of available functionality for the site? Also can the property table have those details i mentioned about facilities and accommodation? Lots of questions i know but its hard to find guides and tutorials for this kind of initial planning.
Thanks Kiteless
stu.

also... is it possible to have 2 foreign keys?

I was thinking, i plan to advertise holiday properties for people who will be my clients. so i need a database to hold their info; one owner(client) may have several properties.

The properties i will be marketing will need a database to store their data.

I will hopefully achieve bookings for their holiday properties, so i need a database to store customer details.

The bookings will need to be recorded and processed so they will need there own database.

So....How about this?

Properties:
propertyID (primary key)
propertyName
propertyAddress
other property details facilities etc
ClientID (owner) (foreign key)

Clients:
clientID (primary key)
clientName
clientAddress
clientContact

Customers:
customerID (primary key)
customerName
customerAddress
customerContact etc..
it would be ideal to be able to see a history for each customer in case of repeat purchase following years. so should this table also have bookingID linked as a foreign key.

Bookings:
bookingID (primary key)
propertyID (foreign key)
customerID (foreign key)
bookingDate


am i getting the hang of it?

Yes a table can have as many foreign keys as necessary. And yes you could add a table for clients in that way.

You would not need to add bookingID to the customers table though. If you wanted to get a history of a customer's bookings you would just query the bookings table to find all bookings for a specific customerID.

mySQL is far superior to Access but is also more difficult to use.

The best databases that I have used in terms of power and scalability are Oracle and MS SQL Server, but both cost money...SQL Server is about $7,000 and Oracle can run up to $40,000 or more.