Database for enquiries/bookings

[Clair]

I have been using a spreadsheet for my enquiries and bookings, but I really want to try and set up a database to get all the info on the screen with minimal left-right scrolling!

These are the fields I use at present in the spreadsheet:

date of enquiry

source of enquiry

arrival date required

depart date required

follow up date

result (0/1/-1)

booking ref

name

add

email add

mobile tel

evening tel

number of adults

number of children

total cost

deposit amount

date deposit received

balance amount

date balance due

date balance received

security deposit received (Y/N)

date security deposit returned

comments

Too many?

Am I missing anything obvious?

Thanks in advance for your suggestions.

[Albert the InfoGipsy]

Clair,

The field list looks sensible, although I don't know what 'result (0/1/-1)' means.

This looks like a classic app that could easily be built in something like MS Access. To keep it simple you could reckon on two data tables (one for the customer details and one for the transactions like deposit payment), plus a couple of lookups, and have a form/subform main screen. That's assuming that you understand basic DB design.

You would end up with the customer details on one side of the screen and the various transactions in a scrollable grid on the other. You could also have a couple of reports to show things like people who are overdue on their deposits.

When I was an Access trainer I used to round off the course by taking a 'real life' requirement from a student and helping the group work through the data and interface design. You could construct the fundamentals of this one in a couple of hours, although making it a bit more foolproof always takes longer.

You could probably also build it in Base (I think thats the Open Office database app) but I've never used that in anger, so can't be sure about how easily you could construct the entry/display screens. In Access most of what I've described can be built using drag & drop and the various wizards.

[LEO]

Clair, do you get that many enquiries to justify a spreadsheet!

I don't get many  enquiries   but convert  probably 1 in 3.

[Théière]

A bigger screen means less scrolling [Www]

[Clair]

Albert,

Thanks for the encouragement. I am a total beginner and although I have used databases in the past (they have been ready-made contact management programs) and I have never built one from scratch before.

Base seems to have a good wiki training site. I'll start there.

You lost me at form/subform![:D]

I like the idea of keeping financial info separate, yet on the screen.

I use 0/1/-1 to indicate whether the conversion is a fail (0), a success (1) or a refusal by me for reasons other than a previous booking (-1).

I just want to get an idea of how other owners deal with info and I'd rather not have to fiddle with the database once it's set up.

Leo,

The spreadsheet helps me to record the data. That works after a fashion,

but it is rather cumbersome to have all these fields along one row on

the screen.

I also want to use the database to help me analyse where the enquiries

come from, which of the sources translate into bookings, how much each

enquiry and each booking eventually cost.

Teapot,

I have a 17" screen. anything bigger wouldn't fit on my lap! [:P]

[Albert the InfoGipsy]

[quote user="Clair"]I have a 17" screen. anything bigger wouldn't fit on my lap! [:P]
[/quote]

You're not eating enough!

[Clair]

[quote user="Albert the InfoGipsy"]

[quote user="Clair"]I have a 17" screen. anything bigger wouldn't fit on my lap! [:P]

[/quote]

You're not eating enough!

[/quote]

I'll try harder! [:P]

[Albert the InfoGipsy]

Clair,

Form/subform design is covered in the wiki that you gave the link for, so it can be done fairly easily in Base.

Essentially, if you enjoy playing with software tyhen I'd suggest you build your own database, otherwise you'll have to hunt around to find a suitable ready-made app. If you need a bit of hand-holding I'd be glad to help.

On the design front, do you get much repeat business? More than one or two repeat customers per year? If so, it would be worth having a three table structure for your main data:

Customers
customer ref (Primary key)
name
add
email add
mobile tel
evening tel
comments

Bookings
booking ref (Primary key)
customer ref (Link to Customers)
date of enquiry
source of enquiry
arrival date required
depart date required
follow up date
result (0/1/-1)
number of adults
number of children
total cost
comments

Transactions
transaction ref
booking ref (Link to Bookings)
Date
transaction type
Amount paid
credit/debit

Your various transactions  become a series of entries in the Transactions table, which makes it easy to add them up and some of the things like 'deposit amount' can be calculated (in this case from 'total amount') rather than stored.

If you haven't met it yet, 'Primary Key' means the unique identifier for a given record in a table. You use it to refer to the data in that table when you need to link tables in queries.

 

[Clair]

That"s great Albert. Thank you ever so much for your input.

I would like to track the source of any contact, so another table would be needed. Would that be feasible?

As not every enquiry translate into a booking, I would need to have a source in the Customer table, which would be carried over into the Booking table.

Advertising

source ref (Primary key) (Link to Customers and Bookings?)

Date of creation

Date of renewal

cost of advert

Number of enquiries

cost per enquiry

Number of bookings

cost per booking

Am I making this too complicated? Over-thinking it?

[Albert the InfoGipsy]

Clair,

No problem. The important thing is to get the data design right first and then you can sort out the front end (forms & suchlike).

You're inputting data that can be derived from other data. That's a waste of effort and source of errors

Advertising
advert ref (Primary key) (Include this field in Bookings to provide link) (customers can be found via Bookings)
Source ref (Links to a lookup table of the various sources you use)
Date of creation
Date of renewal
cost of advert

All these can be derived or calculated from your other data:

Number of enquiries
cost per enquiry
Number of bookings
cost per booking

So you shouldn't try to enter them manually -- they change every time a new enquiry or booking comes in.

You also need to think about how you record your advertising data. If you recycle a record then historic cost per enquiry/booking can get distorted. If you want to be historically accurate you'd create a new record every time you renew. I considered a status flag but the renewal date would be usable.

Sorry if I get a bit pedantic but I'm a data analyst and a lot of people stress the first two syllables of analyst.[geek]

[Clair]

[quote user="Albert the InfoGipsy"]Sorry if I get a bit pedantic but I'm a data analyst and a lot of people stress the first two syllables of analyst.[geek][/quote]

Yeah, I can get like that too, without the data bit... [:P]

I understand what you mean and I will go bear that in mind when I get started.

This is exactly the kind of suggestion I wanted when I put this out here.

I don't think about these things with enough knowledge, why is why your input is so valuable.

Thanks again.

[Clair]

[quote user="Albert the InfoGipsy"]If you want to be historically accurate you'd create a new record every time you renew.[/quote]

This is what I am doing at the moment, but it is a cumbersome way to do things in a spreadsheet.

[Albert the InfoGipsy]

Any real data management becomes incredibly cumbersome in a spreadsheet. Some Excel freaks do amazing things with multiple worksheets cross-referencing each other, but to me it's like using a spanner to drive a nail. 

[osie]

Hi Clair

I sell some software that does the whole booking process including enquiries, ledger generation, linking to your website.. etc etc...

Part of it is a webpage booking form and credit card input form... which is stored in a mysql database on my webserver. 

You could then check it with toad (Toad for MySQL) or write a simple webpage to display the data.

If you know some basic php/html I can send you some of the code you need or if you need database help just let me know.

just pm'ed  you the table layout I use...

Thanks

osie

[Clair]

Osie,

Just seen your PM, thanks.

In total honesty, I am baffled by the language [8-)]

I am in the process of devising my database and I'll stick with slow learning during the off-season... Something to keep me ticking over for the winter!

Thanks again.

PS: no enquiry was sent [;-)]

[osie]

sorry didnt want to be baffling... 

The import script I sent you can be put into the toad application... and would create a table for you where you can enter/store data.

How do you get your bookings.. via telephone, email, or your website.

Which database are you using or would like to use...

I would suggest getting mysql running on your computer and have a play with it... and use toad(http://www.quest.com/mysql/) so it is easy to play with.  Have a go and let me know if you get stuck.

osie