I recently completed an assignment which required me to complete an ER diagram for an apartment complex housing automaker employees. There are multiple automaker companies, and each of these companies have employees. There is a series of apartment complexes owned by the same person that houses these employees. If an employee stays in an apartment complex for over 7 days, a discount is applied that is unique to the company.
Some of the requirements are as follows:
-
Keep track of the new Employees/Trainees and their permanent (Home) addresses and contact information who stay at each of the Alpha Apartment Buildings during a given training session.
-
Keep track of the new Employee's/Trainee's current address and contact information. (What apartment are they residing in?)
-
Keep track of the length of stay for an Employee/Trainee in a specific apartment. This should be based on a Check In and Check Out date.
-
Keep track of all the individual apartments a given Employee/Trainee has ever stayed in. Note: This is historical data. An Employee/Trainee that stays at the one of the Boarding Houses, may get the same apartment or a different one each time that they come to Detroit for training.
-
Keep track of the different apartments that are in each apartment building, along with each apartment number and floor
-
Keep track of the different nightly charges for each of the different apartment types.
-
Keep track of the name, location, address and contact information of each of the apartment buildings
-
Keep track of the address and contact information of the Big Three Automakers
-
Keep track of which Employee/Trainee works at which auto company for billing purposes.
- Keep track of which apartments at each building are available for rental.
My ER-diagram:
As someone new (4 months) to the logical design, I am wondering if there was anything that could be improved on or changed regarding this ER diagram. I have a firm grasp on the concept of relationships, but I get confused when it comes to invoices, order lines, etc.
I also want to make it clear that this assignment has already been turned in and cannot be re-submitted. This is strictly a question out of the desire to learn.
Best Answer
I think overall you have a decent enough design there. My only points of issue might be with the Invoice Table having a calculated field (TotalNights) and that the Discount uses just the DiscountID, not the amount being discounted. Like this, a person looking at the table would have to JOIN to the Discount Table to see what the discount is by itself, unless he wanted to do the manual math of breaking down the Total, Flat Rate and Discount amount.