Database design

[keri]

Hi,

I am starting from scratch with a new database to manage bookings of
units. The database will store customer info, unit info, bookings,
payments etc. I have scetched a basic design and am looking for some
input and some help before I start. Any help or suggestions would be
appreciated.

Tables - Customers, Bookings, Units, PaymentsRecieved
Queries - Unit availability, Payments Due

All the above is fairly simple. However when making a booking I would
like to be able to enter the first payment date, and the payment
frequency (eg weekly, monthly etc) I would then like to be able to
query this information so that I can display a list of payments due
this week / month (it would also query the payments table to see if
the due payment had already been paid). I am unsure how to do this.
(Without entering each and every payment due date as some bookings
could be over 12 months long). Do I need a payments due table?

Further to this I would like to show a calendar of available units (eg
ones not booked / reserved) Any suggestions on whee to start with this
would also be appreciated.

Many thanks in advance.

[Allen Browne]

Hopefully your Booking table has fields like this:
BookingID AutoNumber primary key
CustomerID Number relates to primary key of Customer table
UnitID Number relates to primary key of Unit table.
StartDate Date/Time When first payment is due. Required.
Freq Number how often a payment is due
PeriodID Text type of period (yyyy, m, d, etc.)

Now you need another table that contains a record from 0 to the highest
number of periods you could ever need. It will have one field of type
Number, named (say) CountID, and marked as primary key. Save the table with
a name like tblCount. Now enter a record for each number you need, or you
can use the code in this link to populate the table for you:
http://allenbrowne.com/ser-39.html

Create a query that uses both tables. In the upper pane of query design,
there must be no line joining the 2 tables. This gives you every possible
combination. Type this expression into a fresh column in the Field row:
DueDate: DateAdd([PeriodID], [Freq], [StartDate])
Add criteria under this field for the dates you are wanting to query.

This solution works because the unjoined tables give you every possible
combination, which yields a DueDate for each time a payment is due.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"keri" <ke********@hotmail.comwrote in message
news:11**********************@y42g2000hsy.googlegr oups.com...

>
I am starting from scratch with a new database to manage bookings of
units. The database will store customer info, unit info, bookings,
payments etc. I have scetched a basic design and am looking for some
input and some help before I start. Any help or suggestions would be
appreciated.

Tables - Customers, Bookings, Units, PaymentsRecieved
Queries - Unit availability, Payments Due

All the above is fairly simple. However when making a booking I would
like to be able to enter the first payment date, and the payment
frequency (eg weekly, monthly etc) I would then like to be able to
query this information so that I can display a list of payments due
this week / month (it would also query the payments table to see if
the due payment had already been paid). I am unsure how to do this.
(Without entering each and every payment due date as some bookings
could be over 12 months long). Do I need a payments due table?

Further to this I would like to show a calendar of available units (eg
ones not booked / reserved) Any suggestions on whee to start with this
would also be appreciated.

Many thanks in advance.

[keri]

Many thanks for the help so far. I have done as suggested and all
works fine, the query returns the first duedate for payment after the
specified first payment date. However my brain is still failing me.

Firstly I have a choice of 2 designs;

1. The ability to show next due date (ie. after todays date) however
this limits me to information presuming the last payment has been paid
(the user would have to look elsewhere to find if the most recent due
payment has been paid). However this seems a more simple method.

2. The ability to somehow link the duedate with the payments table, so
that the database links the payments made with the due dates of
payments, and shows the next due date for a payment. If one of the due
payments did not have a linekd payment made record then it would show
the previous payment as being overdue as well as the next payment due
date. This seems more complex, however is more user friendly and
comprehensive.

I would love some advice on which of these scenarios will work, and
how to progress to starting designing these features.

Thanks,

[Allen Browne]

The 2 solutions you propose would both be viable for different scenarios.

You can store the schedule of payments due in a table (your solution #2) if
you desire.
Advantage: You can schedule different dates or amounts (e.g. so a client can
make 2 payments at once, and then miss one.)
Disadvantage: Difficult for open-ended payments or where the amount could
change (e.g. where a membership is due every year for ever, or where
membership fees in future years might be increased.)

Since #2 involves a table with a record for each entry, you can take that
primary key number and use it as a foreign key in your payments table, i.e.
to say, "This payment received is for this particular payment due."

If you use #1, you might consider adding a field to the payments received
table to store the date the actual payment was due (which may be different
to the date the payment was received.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"keri" <ke********@hotmail.comwrote in message
news:11*********************@n39g2000hsh.googlegro ups.com...

Many thanks for the help so far. I have done as suggested and all
works fine, the query returns the first duedate for payment after the
specified first payment date. However my brain is still failing me.

Firstly I have a choice of 2 designs;

1. The ability to show next due date (ie. after todays date) however
this limits me to information presuming the last payment has been paid
(the user would have to look elsewhere to find if the most recent due
payment has been paid). However this seems a more simple method.

2. The ability to somehow link the duedate with the payments table, so
that the database links the payments made with the due dates of
payments, and shows the next due date for a payment. If one of the due
payments did not have a linekd payment made record then it would show
the previous payment as being overdue as well as the next payment due
date. This seems more complex, however is more user friendly and
comprehensive.

I would love some advice on which of these scenarios will work, and
how to progress to starting designing these features.