Jake, as I said, this is a huge question. You are asking how to design a
database that keeps track of:
- types of appointment (e.g haircut, coloration, ...), with the cost and
needed duration for each type.
- customers (people who book appointments)
- staff members
- training units
- which staff members have received which training, when, to what level, and
when they might need a refresher or update
- when staff are normally available to be rostered (days and times, by week?
by calendar month?), with preferred number of hours per week (part/full
time, other), and exceptions (specific dates/times when they are known to be
unavailable),
- rostering needs of your salon (i.e. how many people you anticipate needing
at each timeslot throughout the week/month/period)
- actual rostering (specific dates and times when you have actually rostered
them to work), with what sounds like totally flexible shifts (rather than
specifically granular shifts such as "morning", "afternoon", "evening")
- actual attendance (who showed up for their shifts, including calling in
stand-by staff to fill in when someone is ill or unpredictably unable to
work.)
- appointments: timeslots booked by customers with staff (congruent with the
actual rostering) for the duration consistent with the appointment type,
without double-booking the staff member.
- attendance: whether the client actually showed for the appointment, and
what was actually done (may not be the same as the appointment), and how
long it actually took, including walk-ins who had no appointments.
That's assuming that you don't need to track *any* of the financial aspects
of the business (pay rates per staff member, commissions, shift loadings,
payrolls, purchase orders, arrivals to inventory, back orders, sales,
losses/breakages/returns, current inventory levels and predictions, tax,
etc, etc.)
I am also assuming here that you don't want the software to do the actual
rostering for you: just to provide an interface where you can roster people
manually, and it will alert you to anything that is outside the desired
guidelines. To have it using fuzzy logic to fit all this together for you in
such a way as to be seen to be even-handed in your rostering of the "good"
shifts and the "bad" ones - that would add a whole other layer of
complexity. (It's doable, but not simple, and quite time-consuming.)
Unless you are a professional programmer who has nothing to do for a few
months, it might be worth your while to talk to some other salons, and see
what software they are using to manage their business.
Not trying to discourage you: just to help you identify what's involved
here.
