Database Organization |
Top Previous Next |
A little background will help you understand how reservations and other database elements interact. Some of this may be pretty technical, and it certainly isn't required knowledge to operate the program. But if you prefer to know more about why it works the way it does, then this section will help.
The Campground Master database is a relational database. This means that it consists of multiple tables, which can be related to each other through relational links. There are four main tables in the database -- Sites, Customers, Reservations, and Transactions. (Parks and Operators are also tables, as well as all of the Pick Lists, but they're not important for this discussion.) Each table is a collection of records, where each record represents one item (a customer, a site, etc.), and each record is a collection of fields, where each field is a bit of information (a customer's last name, phone number, zip code, etc.).
In most cases, you will work with the records of a table individually -- for example, when using the Reservation Details dialog, you can only see or change fields in one reservation record at a time. Of course when viewing reports, you are looking at multiple records. Where there is linking going on, it will be transparent to you. But understanding what links do will help you make sense out of some things, like why the Notes column on the Arrivals View doesn't show the notes you just added to the customer's record (because the customer notes are separate from the reservation notes).
This is an example of how the main data tables are related, or linked, to each other:
- Each Reservation record is linked to one Site record and one Customer record.
- In addition, a Reservation can have a list of other "linked" Reservations linked to it.
- Each Transaction links to the Reservation and/or Customer it applies to, if any. The Reservation and Customer also link back to the Transaction (so one reservation can have links to many transactions).
For illustration, here is a tree diagram of a customer with 2 reservations, each having some transactions... and some separate transactions on the customer itself (e.g. for membership dues, since those aren't related to a specific reservation):
--> Customer "Jones"
\--------(link to) Reservation on 7/3
\-----(link to) Customer (Jones)
\-----(link to) Site 1
\-----(link to) Transaction (rent charge)
\-----(link to) Transaction (payment)
\--------(link to) Reservation on 9/1
\-----(link to) Customer (Jones)
\-----(link to) Site 2
\-----(link to) Transaction (rent charge)
\-----(link to) Transaction (payment)
\--------(link to) Transaction (membership dues charge)
\--------(link to) Transaction (membership payment)
Notice how the single Customer record is shown as the top level -- all information for that Customer which is unrelated to when they stay, such as name, phone, address, etc is in that record. There is no reason to have multiple copies of it. It has links to each of the Reservations for that Customer. However, each Reservation is also linked back to the Customer. This duplicate linking isn't strictly necessary, but allows for fast access.
Next are the Reservation records. They only have information specific to that stay, such as the date and Site. Again, the Site information is really just a link to a Site record, since the site details (site name, description, attributes, etc) are only in the Site record, not duplicated for each stay. Likewise, it only has a link to the Customer, since that information is in a separate record.
Then there are the Transaction records. Again, those are separate records that are linked-to. Those can be linked from each Reservation, when they are specific to that particular stay, such as the rent and payment for that stay. However, there can also be Customer transactions that are unrelated to a particular stay, such as membership dues. Note that one of the common errors made is putting a payment for a reservation on the Customer instead of the Reservation. This causes confusion because the Reservation's balance won't be correct, since the payment didn't get applied to that particular stay. See the separate topic under Working with Transactions for Reservation vs Customer transactions.
The Reservation table is the primary object you will be using, from which everything related to it can be reached. When a report shows the customer name and phone number for a reservation, that information is actually coming out of the Customer table, through the customer link in the Reservation. Thus if the information is changed for the Customer, it will be seen as changed for every reservation that the customer has ever made.
The advantages of relational databases are many, but primarily it helps keep data consistent and allows the database to be much smaller since unique information is only stored once.