L
Lorenzo
Hello Everyone,
I have a database that I use to keep track of Reservations for my rental
Agency. The application so far has worked as planned but I am not sure if I
have done things correctly. I really need to understand if I have made some
planning mistakes that will bring the application in the future to don't
work properly.
It is a medium-complex database with a core part that I need to be 100% sure
it is well done since mistakes here will cause the entire appl to fall
apart.
There application has a main form that handles reservation. The mainForm is
made of one frmReservation and three subForms related only to the mainForm
and not to each other.
this is the core part:
tblReservation
IDReservation, intReservationNumber, dteReservation, ....., IDClient,
IDApartment, IDAgency ...so the reservation has a Client, an Apartment, etc.
tblInvoices ' the table for invoices is very crucial since a Reservation
can have multiple invoices: one for deposit, one for balance...
IDInvoice, intInvoiceNumber, dteInvoiceDate, curAmount,.....IDReservation *
* !!! IMPORTANT !!! I have included IDReservation here in tblInvoices so I
can
have multiple Invoices for that Reservation but, is this correct?, is this
the right way to do it? In the relationships I have applied the Referential
Integrity plus checked the two options Cascade update and delete Related
Fields between tblReservation and tblInvoices. This because I want to
update/delete the invoice/invoices whenever I delete that Reservation.
The type of JOIN I have used is the OUTER since I can really select all the
records from both tables.
tblCleanings ' This is another crucial table in my appl
IDCleaning, intCleaningNumber, dteDateCleaning, ....., IDReservation
Here again I need to have multiple cleaning services for that one
Reservation but here for example I might also have cleanings not related to
any Reservation maybe just because a cleaning can be just for maintenance/
other. In fact in my app, from this table, has another Form independent
from this mainReservationForm to add cleaning services. To achieve this
result I have applied again the Referential Integrity and the Cascade update
and delete Related Fields but the type of Join here is a RIGHT-OUTER JOIN so
I can still have cleaning services not related to any Reservation but I can
still delete those cleaning services correlated to a Reservation. We may
apply the same rule to the above case for invoices, I might have in the
future to relate an invoice to other then Reservations, correct?
tblExtras
idExtra, blnLateCheckIn, blnCellularPhoneRent, blnotherService
....IDReservation
Here I only need one correlated field since only one set of services is
bounded to that Reservation
Ok the frmReservation (the mainForm holding the 3 subForms) is generated by
the following:
qryReservation that is based on tblReservation plus the join with tblClients
and other tables such as tblApartments, tblAgencies...etc. From those
tables I only retrieve the values I need to address a client, an apartment,
an Agency to that Reservation.
The first subForm is generated by tblInvoices. Here I can add/delete as
many invoices for that IDReservation.
The second subForm generated by tblCleanings and functions same as above.
The third by tblExtras
On a testing database this works fine but I ask you if this is formally
correct or there is another method to achieve this.
With this scenario will I encounter problems or it is correct and I should
keep working on like this?
If I am doing something completely wrong please point it out to me so I can
start working in the right direction. I am thinking of many-to-many
relationships ... Am I in one of those cases? Do I need to create a
"Junction Table". If I do please let me know where to find tutorials on
this. I am not too familiar with it.
Thanks for all your precious help.
Lorenzo
Firenze
I have a database that I use to keep track of Reservations for my rental
Agency. The application so far has worked as planned but I am not sure if I
have done things correctly. I really need to understand if I have made some
planning mistakes that will bring the application in the future to don't
work properly.
It is a medium-complex database with a core part that I need to be 100% sure
it is well done since mistakes here will cause the entire appl to fall
apart.
There application has a main form that handles reservation. The mainForm is
made of one frmReservation and three subForms related only to the mainForm
and not to each other.
this is the core part:
tblReservation
IDReservation, intReservationNumber, dteReservation, ....., IDClient,
IDApartment, IDAgency ...so the reservation has a Client, an Apartment, etc.
tblInvoices ' the table for invoices is very crucial since a Reservation
can have multiple invoices: one for deposit, one for balance...
IDInvoice, intInvoiceNumber, dteInvoiceDate, curAmount,.....IDReservation *
* !!! IMPORTANT !!! I have included IDReservation here in tblInvoices so I
can
have multiple Invoices for that Reservation but, is this correct?, is this
the right way to do it? In the relationships I have applied the Referential
Integrity plus checked the two options Cascade update and delete Related
Fields between tblReservation and tblInvoices. This because I want to
update/delete the invoice/invoices whenever I delete that Reservation.
The type of JOIN I have used is the OUTER since I can really select all the
records from both tables.
tblCleanings ' This is another crucial table in my appl
IDCleaning, intCleaningNumber, dteDateCleaning, ....., IDReservation
Here again I need to have multiple cleaning services for that one
Reservation but here for example I might also have cleanings not related to
any Reservation maybe just because a cleaning can be just for maintenance/
other. In fact in my app, from this table, has another Form independent
from this mainReservationForm to add cleaning services. To achieve this
result I have applied again the Referential Integrity and the Cascade update
and delete Related Fields but the type of Join here is a RIGHT-OUTER JOIN so
I can still have cleaning services not related to any Reservation but I can
still delete those cleaning services correlated to a Reservation. We may
apply the same rule to the above case for invoices, I might have in the
future to relate an invoice to other then Reservations, correct?
tblExtras
idExtra, blnLateCheckIn, blnCellularPhoneRent, blnotherService
....IDReservation
Here I only need one correlated field since only one set of services is
bounded to that Reservation
Ok the frmReservation (the mainForm holding the 3 subForms) is generated by
the following:
qryReservation that is based on tblReservation plus the join with tblClients
and other tables such as tblApartments, tblAgencies...etc. From those
tables I only retrieve the values I need to address a client, an apartment,
an Agency to that Reservation.
The first subForm is generated by tblInvoices. Here I can add/delete as
many invoices for that IDReservation.
The second subForm generated by tblCleanings and functions same as above.
The third by tblExtras
On a testing database this works fine but I ask you if this is formally
correct or there is another method to achieve this.
With this scenario will I encounter problems or it is correct and I should
keep working on like this?
If I am doing something completely wrong please point it out to me so I can
start working in the right direction. I am thinking of many-to-many
relationships ... Am I in one of those cases? Do I need to create a
"Junction Table". If I do please let me know where to find tutorials on
this. I am not too familiar with it.
Thanks for all your precious help.
Lorenzo
Firenze