Description

Customer Account Signup process: When a new customer is reserving a room or checking in, the Sour Apple staff will setup an account for that person. This requires a first and last name, email, address, and a single phone number. This account is used to automatically track their stay credits earned. Each night stay (per room) earns a single credit. 10 credits can be redeemed for a free night’s stay in one room. The team tracks total credits earned and credits used to understand how many credits a person has left to use currently while retaining a simple history of how many they’ve earned in a lifetime. Each customer will have a single credit card on file since Sour Apple keeps things simple and doesn’t want to track multiple payments for customers.
rating of 1 to 5 stars that can be captured and stored on the reservation if they complete the rating survey.

Technical Details: NOTE: Each of these could be separate entities.
• Reservations – Once the reservation is made, a reservation total is saved on the reservation which is the number of nights times the rate of each room added up. The confirmation number has to be unique to easily find a reservation. It’s a random collection of numbers and letters and looks something like “G2JD8J3”. The status that is attached to the reservation is one of the following preset single characters codes (e.g. U for upcoming, I for in progress, C for completed, and N for no show, R for refunded). Discount codes are usually a max of 12 characters and are a combo of numbers and letters. Customer rating is just a number of 1 to 5 so we can capture averages over time by location and customer. For each reservation we also will track a “notes” column that is not to exceed 500 characters that can be used to track random things like customer requests or special celebrations and more.
Deliverable: The database model should include table names, field names, and identify which fields are primary and foreign keys. Relationships between tables should use the Crow’s Foot method.

Formatting Tips/Requirements:
• When you connect your tables, be sure that the relationship lines are attached and connecting to the specific columns on the table. This way, you are showing that a PK or FK on one table is connected to corresponding PK/FK on the other table. The feet shouldn’t be touching random parts of the table.
• When ordering the columns in your entities, it’s best to list the PKs as the first columns, the FKs as the next ones, and then the rest of your non-key columns below that. This keeps it neat and makes it easy to quickly identify what the table is about.
• Remember that a column can be an FK and also part of a composite PK. If that’s the case, you can use “PK,FK” for any columns that are both part of a composite PK and are also an FK.