916 Medical Skin Studio
Design Document
Kyle Sunde
Ryan Such
Matt Edwards
Will Weyrich
Houa Lee
Table
of Contents
I. Problem Statement……………………………………………….2
II. 1. Entity Relationship Diagram……………………………..3
2. Object
Model Diagram…………………………………….4
III. User Views………………………………………………………5-17
IV. Data
Dictionary…………………………………………………18-27
V. Use Cases…………………………………………………………28-32
VI. Scenarios…………………………………………………………33-50
I.
Problem Statement/Requirements Definition:
916 Medical Skin Studio is a local medical spa owned by Tatja Renee. They are an exclusive medical spa that offers a high standard of professionalism and exceptional one on one customer care in a comfortable, relaxed setting. Tatja needs a website that is aesthetically appealing to advertise her business. She also needs a database to process client transactions and maintain client history in a quick and efficient manner. When clients are paying or their services or scheduling an appointment, she wants them to be able to navigate a clean and organized interface. Client transactions should be quick and easy to process.
Our project consists of building a website and a database for Tatja to use for easy access to what she needs to manage her business. It will store patient records, be able to manage appointments, and allow her patients remote functionality over the Internet such as requesting appointments and viewing available products and services. The website will advertise products and services that the spa offers to her clients, as well as manage and store client information and visit history.
II.
Entity-Relationship
Model
1. Entity-Relationship Model:
2.
Object Model
diagram:
In order to make the object model diagram
readable, many of the objects’ entities have been left out but are further
specified in the data dictionary.
III. User Views
1. Database Forms
Form: Medical History Item
Form #: 1
Description: A custom "medical history" item containing a category and information pertinent to it, such as "Music" "likes Jazz, classical" or "Marriage" "Divorced, two children, live with husband". Clients could have any number of these associated with their file.
Form: Reminder
Form #: 2
Description: A form creating a reminder in the system for a service for a particular patient.
Form: Open/Edit Register
Form #: 3
Description: Stating the amount of change in the drawer at the beginning of a shift
Form: Close Register
Form #: 4
Description: Input the totals from the drawer to confirm register is balanced. Once balanced, form would allow closing the register.
Form#: 5
Form: Patient Information
Description: This form will be
used to add and edit information about patients. Patient Numbers will be generated by the
system randomly. All other information
will be able to be accessed and edited by administrators, staff, and the
client.
Form#: 6
Form: Employee Information –
Description: This form will be used to add and edit employee information. If adding a new employee, a employee ID number will be generated by the system. Only Administrators will be allowed to edit pay information.
Form#: 7
Form: Inventory Information
Description: This form will be used to add and edit inventory and services available to the client. Item Codes can either be generated randomly by the system or input manually by Administrators. For safety, only administrators or select staff will be able to edit inventory pricing information.
Form#: 8
Form: Package Information -
Description: This form will be used to enter information about service packages. Package numbers can either be generated randomly by the system or input manually by staff or administrators. Every Item entered will have a corresponding quantity associated with it.
Form#: 9
Form: Schedule Appointment -
Description: This form will be
used along with a calender system so that staff,
administrators or clients can enter the data relevant to scheduling an
appointment.
Form#: 10
Form: Gift Certificate -
Description: This form will be used to enter gift certificates into the database. If the gift certificate is being purchased, an ID number will be generated by the system. All other information will be entered by staff or administrators. If the gift certificate is being redeemed, the user will have to enter the id number of the gift certificate being redeemed.
Form#: 11
Form: Line Item -
Description: This form will be used to enter single item transactions. This information can be entered by staff or administrators. Every time a line item is entered, the system will generate a unique line item ID number.
Form#: 12
Form: Ticket Information -
Description: This form will be used to add ticket information to the database. The system will generate the ticket number randomly. The ticket form will have the ability to access line items form so that line items can be added to the total ticket cost. This form will be able to be used by staff and administrators.
Form#: 13
Form: Payment Information -
Description: This form will be
used to enter payment information.
Payment Numbers will be generated randomly by the system. This form will change dynamically depending
on what kind of payment type entered.
Form#: 14
Form: System Settings -
Description: This form will be used in the beginning stages of the databases use in order to store information needed in order for the system to run correctly. This information can only be added and edited by administrators.
2. DataBase
Reports
Report #: 15
Report: Deposit Slip Report
Description: An itemized list of check and credit card transactions for a specific date or drawer. A totals field at the bottom for all payment types. Useful for reconciling against physical check & credit card slips.
Report #: 16
Report: Register Transaction Report
Description: An Itemized list of all Invoices and related payments for a specific date or drawer batch. Totals at the bottom.
Report #: 17
Report: Employee Production Report
Description: A list of products & services credited to a specified employee over a specified date range.
Report #: 18
Report: Company Production Report
Description: A list of products & services credited to the company over a specified date range. Can be subtotaled by specific employee(s).
Report #: 19
Report: Inventory Report
Description: A list of products (NOT Services) showing the date last sold, and the amount currently in stock.
Report: Appointment Report
Report #: 20
Description: A list of appointments, "subtotaled" either by date of appointment, or by employee the appointment is scheduled for.
IV. Data Dictionary
·
History_Item
o client_id
§
The unique identifier that relates the history item
to the client.
§
Primary key
§
int
o
item_name
§
The
name of this client history item, used as custom note about the client
§
Primary
key
§
varchar(16)
o
value
§
The
description of the history item.
§
varchar(400)
·
Patient
o
patient_id
§
A
unique patient id number.
§
Primary
key
§
int
o
first_name
§
The
patient’s first name.
§
varchar(16)
o
last_name
§
The
patient’s last name.
§
varchar(16)
o
e-mail
§
The
patient’s e-mail address.
§
varchar(16)
o
address_street
§
The
patient’s street address.
§
varchar(16)
o
address_state
§
The
patient’s state.
§
varchar(16)
o
address_city
§
The
patient’s city.
§
varchar(16)
o
address_zip
§
The
patient’s zip code.
§
int
o
primary_phone_number
§
The
patient’s primary phone number.
§
varchar(16)
o
secondary_phone_number
§
The
patient’s secondary phone number.
§
varchar(16)
o
website
§
The
patient’s website.
§
varchar(16)
o
picture
§
The
patient’s picture.
o
dob_day
§
The
patient’s day of birth.
§
int
o
dob_month
§
The
patient’s month of birth.
§
int
o
dob_year
§
The
patient’s year of birth.
§
int
o
username
§
This
unique user name is used as the patient’s log-in for the website.
§
Primary
key
§
varchar(16)
o
password
§
This
is the patient’s password that corresponds to their user name.
§
varchat(16)
o
last_visit_month
§
The
month of the patient’s last visit.
§
int
o
last_visit_day
§
The
day of the patient’s last visit.
§
int
o
last_visit_year
§
The
year of the patient’s last visit.
§
int
o
em_name
§
The
emergency contact’s name.
§
varchar(16)
o
em_relation
§
The
relation of the emergency contact to the patient.
§
varchar(16)
o
em_number
§
The
emergency contact’s phone number.
§
varchar(16)
·
Register
o
register_no
§
The
unique register number, assigned to an employee.
§
Primary
key
§
int
o
date_day
§
The
day of the register action.
§
int
o
date_month
§
The
month of the register action.
§
int
o
date_year
§
The
year of the register action.
§
int
o
sub_
total_cash
§
The
sub total of the register action if cash was used.
§
decimal(5,2)
o
total_cash
§
The
ammount of cash in the register action.
§
decimal(5,2)
o
sub_
total_check
§
The
sub total of the register action if a check was used.
§
decimal(5,2)
o
total_check
§
The
total of the register action payed by check.
§
decimal(5,2)
o
sub_total_ccard
§
The
sub total of the payment by credit card.
§
decimal(5,2)
o
total_ccard
§
The
total of the credit card payment.
§
decimal(5,2)
o
total_payments
§
The
total payment of the register action.
§
decimal(5,2)
·
Payment
o
payment_no
§
The
payment identification number.
§
Primary
key
§
int
o
change
§
The
change given to the client.
§
decimal(5,2)
o
total_payment
§
The
total payment by the client.
§
decimal(5,2)
o
employee_tip
§
The
employee tip given by the client.
§
decimal(5,2)
o
type
§
The
type of payment
§
check
/ cash / ccard / gift_certificate
§
varchar(16)
o
gift_certificate_no
§
The
number of the gift certificate (if used).
§
int
o
check_no
§
The
check number.
§
int
o
credit_no
§
The
credit card number.
§
int
·
Reminder
o
create_day
§
The
creation day.
§
int
o
create_month
§
The
creation month.
§
int
o
create_year
§
The
creation year.
§
int
o
due_day
§
The
day the reminder will be issued.
§
int
o
due_month
§
The
month the reminder will be issued.
§
int
o
due_year
§
The
year the reminder will be issued.
§
int
o
description
§
The
description of the reminder.
§
int
o
reminder_id
§
The
unique id for a reminder
§
Primary
/foreign key.
§
int
·
Reminder_clients
o
reminder_id
§
The
unique reminder id.
§
Primary/foreign
key.
§
int
o
client_id
§
Keeps
track of the clients that the reminder is going to be issued for.
§
int
·
Appointment
o
date_day
§
The
day of the appointment.
§
int
o
date_month
§
The
month of the appointment.
§
int
o
date_year
§
The
year of the appointment.
§
int
o
start_time_hour
§
The
start time of the appointment by hour.
§
int
o
start_time_minutes
§
The
start time of the appointment by minutes.
§
int
o
duration
§
The
length of the appointment in minutes.
§
int
o
patient
§
The
name of the patient.
§
varchar(16)
o
patient_id
§
The
patient’s id, if they have an account set up for the website.
§
Foreign
key.
§
int
o
employee_id
§
The
employee id that will be giving the treatment.
§
Foreign
key.
§
int
o
service
§
The
service that the appointment is for.
§
varchar(16)
o
status
§
The
status of the appointment.
§
missed / attended.
§
varchar(16)
o
appointment_id
§
The
appointment number.
§
Primary
key.
§
int
·
Employee
o
employee_no
§
The
unique employee number.
§
Primary
key.
§
int
o
register_no
§
The
unique employee register number.
§
foreign key.
§
int
o
first_name
§
The
employee’s first name.
§
varchar(16)
o
last_name
§
The
employee’s last name.
§
varchar(16)
o
Picture
§
A
picture of the employee.
o
schedule_id
§
The
employee’s schedule identifier. Corresponds to the schedule table.
§
Foreign
key.
§
int
o
commission_rate
§
The
commission if the employee is payed on commission.
§
decimal(5,2)
o
hourly_pay
§
The
hourly pay of the employee if they are an hourly worker.
§
decimal(5,2)
o
salary_pay
§
The
salary of the employee if they are payed on salary.
§
decimal(5,2)
o
active
§
The
status of the employee.
§
yes
/ no
§
varchar(16)
o
category
§
The
type of employee.
§
varchar(16)
o
username
§
The
employee’s username.
§
Primary
key.
§
varchar(16)
o
Password
§
The
employee’s password.
§
varchar(16)
·
Ticket
o
ticket_id
§
The
unique ticket identifier.
§
Primary
key.
§
Int
o
patient_id
§
Identifies
the patient that is being charged.
§
int
o
day
§
The
day that the ticket was issued.
§
int
o
month
§
The
month that the ticket was issued.
§
int
o
year
§
The
year that the ticket was issued.
§
int
o
total_cost
§
The
total cost of the service.
§
decimal(5,2)
o
total_tax
§
The
tax on the service.
§
decimal(5,2)
o
tip
§
A
tip for the service as indicated by the client.
§
decimal(5,2)
·
Package
o
package_no
§
The
number of the package.
§
Primary
key.
§
int
o
package_name
§
The
name of the package.
§
varchar(16)
o
package_desc
§
The
package description.
§
varchar(400)
o
package_discount
§
The
package discount, if any.
§
decimal(5,2)
o
items_included
§
A
list of the items included in the package.
§
varchar(400)
·
Package_items
o
package_no
§
The
package number that the package items belong to.
§
Primary
/ foreign key.
§
int
o
item_included
§
The
item included.
§
varchar(16)
o
item_code
§
The
code of the items included.
§
Foreign
key.
§
int
o
item_quantity
§
The
quantity of the item included.
§
int
·
Inventory
o
item_code
§
The
code of the item in the inventory.
§
Primary
key.
§
int
o
name
§
The
name of the item.
§
varchar(16)
o
description
§
The
description of the item.
§
varchar(400)
o
cost
§
The
cost of the item to the spa (may be same as wholesale).
§
decimal(5,2)
o
wholesale
§
The
wholesale cost of the item.
§
decimal(5,2)
o
retail
§
The
retail value of the item.
§
decimal(5,2)
o
active
§
yes/no
§
varchar(16)
o
category
§
The
category of the item. If it is a service the item_code
will be used to look up the corresponding service_id.
If it is a product the item_code will be used to look
up the corresponding product_id.
§
service
/ product
§
varchar(16)
·
Service
o
service_id
§
The
unique service id.
§
Primary
key.
§
int
o
service_description
§
The
description for the service.
§
varchar(400)
o
service_length
§
The
length of the service in minutes.
§
int
o
providing_employees
§
The
employee id’s that can provide the service.
§
int
·
Product
o product_id
§
The unique product id.
§
Primary
key.
§
int
o
Taxable
§
Is
this product taxable?
§
yes
/ no
§
varchar(16)
o
inventory_count
§
The
ammount of products in the inventory.
§
int
·
Line_item
o line_id
§
The unique id for the line item.
§
int
o item_type
§
The type of line item.
§
varchar(16)
o performed_sold
§
Was the line item performed (service) or sold?
§
performed / sold
§
varchar(16)
o item_type
§
The service or product type of line item.
§
service / product / gift_certificate
/ ticket
§
varchar(16)
o item_id
§
The item’s unique identifier.
§
Foreign key.
§
int
·
Gift_Certificate
o gift_id
§
The gift certificate’s unique identifier.
§
primary key
§
int
o day_purchased
§
The day that the gift certificate was purchased.
§
int
o month_purchased
§
The month that the gift certificate was purchased.
§
int
o year_purchased
§
The year that the gift certificate was purchased.
§
int
o amount
§
Amount of balance remaining on the gift certificate.
§
decimal(5,2)
o from
§
The patient_id of the gift
sender.
§
foreign key
§
int
o to
§
The patient_id of the
gift’
§
foreign key
§
int
o tip
§
The tip included in gift certificate.
§
decimal(5,2)
o date_redeemed
§
The date that the certificate was redeemed.
§
varchar(16)
·
System_Settings
o
cmp_name
§
The
company name.
§
varchar(16)
o
cmp_address
§
The
company address.
§
varchar(64)
o
cmp_phone
§
The
company telephone number.
§
varchar(16)
o
state_tax
§
The
state tax.
§
decimal(5,2)
o
local_tax
§
The
local tax.
§
decimal(5,2)
o
require_register
§
Is
a register required?
§
yes
/ no
§
varchar(16)
V.
Use Cases
Use Case Diagram:
The following are the use cases for the spas database and website that are being implemented:
Use case descriptions:
|
Use Case: |
Add/Edit Client Info |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
The administrators and clients will be able to add and edit client info such as names, addresses, phone numbers, contact information, etc. The employees can only add client information but will not be able to edit it other than adding comments. This allows storage and collection of data that will help the employees provide a better more personal experience for the client. Since the information with the client is important and subject to change, it may not be sufficient to have only the owner in charge of keeping it up to date. |
|
Use Case: |
View Client Info |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
Administrators, employees, and clients are all able to view client information. |
|
Use Case: |
Add/Edit Referrals |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
The client can specify who referred them to the spa. Administrators and employees can also add a referral if the client doesn’t specify a referral themselves but was referred to the spa by another customer. Referrals will be stored in each client’s information. |
|
Use Case: |
View Referrals |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
Administrators and employees can view any client’s referrals. A client can only view their own referrals. |
|
Use Case: |
Add/Edit Appointments |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
Administrators and employees can schedule appointments directly and have them saved to the calendar. Clients will be able to request certain appointment times but are unable to directly add an appointment without employee/administrator approval. Appointments are not guaranteed until an employee or administrator contacts the client. The appointment will then be inserted into the appointment table. The administrator and employee can both cancel any appointment. The client can request to cancel their own appointments. |
|
Use Case: |
View Appointments |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
Administrators and employees will be able to view any appointments in a given time frame in calendar format. Hours that are available on a requested date will be shown to the client along with their already scheduled appointments. |
|
Use Case: |
Add/Edit Tickets |
|
Actor(s) |
Administrator, Employee |
|
Description: |
An administrator or employee will add or change tickets. Tickets will include the total cost of the treatment and products, the tax, the tip, ticket number, date and patient ID. Tickets will also note payment type including cash, credit, gift certificate, etc. |
|
Use Case: |
View Tickets |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
An administrator or employee can view any client’s tickets. A client can only view their own tickets. |
|
Use Case: |
Add/Process Gift Certificate |
|
Actor(s) |
Administrator, Employee |
|
Description: |
An administrator or employee can add a gift certificate. They can create a gift certificate by designating the amount or service(s) and confirming payment for the gift certificate. Gift certificates will be given an ID number, a value or service(s), and will indicate who they are for and from. Once a gift certificate is processed, the ID will become void in the system. A client’s ticket will indicate what gift certificate was used on it. |
|
Use Case: |
Add/Edit Services/Products |
|
Actor(s) |
Administrator |
|
Description: |
If new services or products become available, only the administrator can add them. They are also capable of editing or removing current products or services. Each service or product will be noted by a corresponding ID number, price (retail and wholesale), and description. |
|
Use Case: |
View Services/Products |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
Administrators and employees can view the service and product wholesale values. All of the service and product information such as the ID and description are available for view as well. The client will see a simplified version of the information with the description and retail value. |
|
Use Case: |
View Client Purchase History |
|
Actor(s) |
Administrator, Employee, Client |
|
Description: |
Report would be run for a specific client for a specified date range. Report would list invoice (ticket) & payment history for the client in chronological order. Tickets could be listed in detail (all line items showing) or summary (only ticket header (date/total/main reason)) formats. A running balance down the side would be calculated. |
|
Use Case: |
Generate Client Reminders |
|
Actor(s) |
Administrator, Employee, System |
|
Description: |
Report could be run to select Patients based up on Last Visit, specific services received, birthdays upcoming, missed appointments, etc. Output would be a comma delimited file OR formatted for mailing labels and include Patient Name, Patient Address, Patient Number. |
|
Use Case: |
Email Client Reminders |
|
Actor(s) |
Administrator, System |
|
Description: |
The system will automatically notify the client based on the e-mail reminders already generated. The administrator will also be able to e-mail the clients and their own discretion. The generated e-mails sent by the system could include scheduled appointments, birthday reminders, and other notifications. |
|
Use Case: |
Generate Deposit Slip |
|
Actor(s) |
Administrator, Employee, System |
|
Description: |
Run for a specific day (or register, if that feature is active). Deposit slip would list all individual check and bankcard transactions processed during that time. Patient Name, Patient Acct #, Card Type/Check # and amount. Report would be subtotaled by Checks and by Bank Cards. A Cash total would be included, though not broken out by transaction. This report is useful for finding errors when balancing the drawer. |
|
Use Case: |
Generate Appointment Report |
|
Actor(s) |
Administrator, Employee, System |
|
Description: |
Report could be run for one or more days. Report could be run for one or more employees. Output would be list of appointments during specified time period booked for specified employee. Data would include Patient Name, Patient Acct #, reason for visit (service booked), time allotted, and patient phone number. Appointments could be filtered by Closed/Missed status. |
|
Use Case: |
Generate Inventory Report |
|
Actor(s) |
Administrator, Employee, System |
|
Description: |
Report would be run off of “In Stock” quantity for products and could be run for those below a certain value on hand. Data would include Product Code, Product Name, Quantity on Hand, Date Last Sold. |
|
Use Case: |
Generate Register Transaction Report |
|
Actor(s) |
Administrator, Employee, System |
|
Description: |
Run for a specific day (or register, if that feature is active). Register transaction would list date, Ticket #, Patient, ticket total, payment amount, payment type, card type/check #, and transaction total (ticket amount – total amount). Report would need to handle multiple payments on a single Ticket, also multiple tickets per payment. |
|
Use Case: |
Generate Employee Production Report |
|
Actor(s) |
Administrator, Employee, System |
|
Description: |
An administrator or the system can create production reports for any given employee. An employee can generate production reports only for themselves. They will be able to specify the date range (day, week, month, etc.) and it will include items credited to that employee during that time period and payroll information. |
|
Use Case: |
Generate Company Production Report |
|
Actor(s) |
Administrator, System |
|
Description: |
An administrator or the system will be able to specify date range (to run for day, week month, etc.), and employees, either singly or in combination, or for the company as a whole to run a report for line items credited to that employee(s) during the specified time period along with payroll information. If run for specific employees, will be subtotaled by employee. |
VI.
Scenarios
Add/Edit Client Info
1. Client/Employee
selects ‘edit information’ from the interface while selecting the correct
client to update, or in the case of the client, making sure they are logged in
and on their account page.
2. Information
is edited/updated, user clicks ‘Save Changes’ to keep new information.
View Client Info
1. User
selects ‘View Information’ from the interface while selecting the desired
client. In the case of the client
themselves, they must be logged in and on their account page, then select ‘View
Information’.
2. Information
is displayed, user clicks ‘Return’ to traverse back.
Add/Edit Referrals
1. User
selects ‘Add Referral’ from the interface while selecting the desired client to
add a referral to. In the case of the
client, they must be logged in and on their account page, and select ‘Add
Referral’.
2. Referral
information is added/edited, user clicks ‘Save Changes’ to keep new
information.
View Referrals
1. User
selects ‘View Referrals’ from the interface while selecting the desired client
to view. A client can view their own
referrals while being logged into their account.
2. Information
is displays, user clicks ‘Return’ to traverse back.
Add/Edit Appointments
Administrators/Employees
1. User
selects ‘Add Appointment’ from the interface while selecting a specific date
from the calendar.
2. User
adds information such as time, treatment, and additional notes. When finished, user selects ‘Save
Appointment’.
3. Information
is updated on the calendar/appointment screen.
Clients
1. User
selects ‘Request Appointment’ while being logged into their account.
2. On the
appointment page, user adds information such as date, time, and treatment
requested. Any additional notes can be
added as well. When finished user
selects ‘Send Request’.
3. Information
is updated; current appointments requested will be displayed on the main
account page.
View Appointments
1. User
selects ‘View Appointments’ from the client screen while selecting the desired
client. In the case of the client, the
client must be logged in and select ‘View Appointments’ from their account
page.
2. Information
on current appointments scheduled is displayed in a calendar format. Hours available for an appointment will also
be shown.
Add/Edit Tickets
1. An
Administrator or Employee will be able to select ‘Add Ticket’ while selecting
the desired client, or ‘Edit Ticket’ while selecting the desired ticket on the
‘View Tickets’ screen for that client.
2. Information
about the ticket such as cost, tax, tip, number, date, patient ID, payment type will be added/updated. User selects ‘Save Ticket’ and returns to the
main page.
View Tickets
1. User
selects ‘View Ticket’ while selecting the desired client. In the case of the client, the client must be
logged in and on their account page, then select ‘View Tickets’.
2. Ticket
information is displayed, user clicks ‘Return’ to traverse back.