Babysitter Example Dimensional Modeling

Babysitter Example Dimensional Modeling

Babysitter Service The MISSA Service Club wants to run a babysitting service. Customers call to request a sitter and the Club Coordinator assigns an employee to sit for the customer from a list of employees available for the particular day requested.

Usage Customers call in to request an appointment The coordinator checks to see if the customer is registered and enters the appointment in the system The coordinator finds an employee to take the appointment The club sends a confirmation to the employee and the customer Once the engagement is complete the club bills the customer for the service.

ERD Customer CNumber LastName FirstName Address City State Zip Phone Job JobNumber ENumber CNumber Date TimeBegin TimeEnd Rate Notes Employee ENumber ClubNumber LastName FirstName Address City State Zip Phone DaysAvailable Club ClubNumber ClubName ClubAccount

Entity Relationship Model MINIMIZES DATA REDUNDANCY Easier to insert, delete and update data Each “thing” in the system has its own table Each row in the table is uniquely identified with a primary key Attributes (variables) are properties of the entity Rows in different tables are connected by matching primary keys Retrievals use professionally written, sometimes complex SQL statements

Dimensional Model Customer CustomerKey LastName FirstName Address City State Zip Phone Date DateKey SQLDate DayName Holiday Job JobNumber EmployeeKey CustomerKey ClubKey DateKey TimeBegin TimeEnd Amount Duration Employee EmployeeKey ClubNumber LastName FirstName Address City State Zip Phone Club ClubKey ClubName ClubAccount

Dimensional Model MAXIMIZE RETRIEVAL EFFICIENCY Produces simpler, faster retrievals One central fact table per model A fact represents an event or item that the user wants to track Fact data is usually numeric and additive Descriptive properties are connected with dimension tables Queries involve simple SQL code written by users