October 24, 2014 at 9:29 am
Dear experts,
Obviously, I am not a database design guru as you can probably see from sample below.
We are building an app that allows the public to reserve a facility in a park.
A user checks for availability of a facility and reserves a facility for a time slot in a particular day.
I have designed a database that includes FEES table, USERS table, CUSTOMERS table, FACILITIES table, and RESERVATIONS table.
Users are expected to make a certain deposit amount.
I added a fieldname called ReservationDeposit and put it in the RESERVATIONS table.
My question is whether putting that field in RESERVATIONS table is the most appropriate place to put it?
Any ideas is greatly appreciated.
Facilities
facilityCode
facilityName
facilityType /* park, pavilion, ... */
facilitySize /* small, medium, large, N/A */
facilitySizeMin /* minimum party size */
facilitySizeMax /* maximum party size */
facilityOpen /* I suppose what time facility is open? */
facilityClose /* I suppose what time facility is closed? */
facilityImageURL /* url where image will be stored */
Facility_Availability
facilityCode
facilityAvailabilityDate
beginTime
endTime
totalMinutes_Available (computed as [end time] - [begin time])
Reservations
reservationId
facilityCode
partyId
reservedBy
description
dateReserved /* use system date */
resBeginTime
resEndTime
reservationAmount
reservationAddOns /* what is that? */
PaymentMethod
ReservationDeposit
Reservation_Fees
reservationId
date
feeId
feeActual_Amount
Reservation_Special_Handling
reservationId
special_HandlingId
description /* allergies / assistance animals / handicapped accomodations / medicines required on-site / etc. */
Fees
feeId
feeCategory /* facility rate / party charge / group type charge / Afterhour charge/ deposit... */
feeType /* facility type | party type | group type |... */
feeAmount /* fee standard amount */
Parties
partyId
reservationId
reservationType /* Profit / Non Profit / Non-City Citizens / ... */
minNumber /* minimym number of people */
maxNumber /* maximum number of people */
October 24, 2014 at 9:38 am
A very quick observation shows you have a Fee table and one category would be deposit.
Lets say you insert a deposit value into the Reservations and Fees table and you later perform an update to only the Fees table, you now have two different deposit amounts - Which one is correct? No way for you application to know...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply