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: 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.

  • Very good job. I think John pointed out the main key points that I would as well require slight work. Also, do you happen to be MayhemMercenary? If so, my son can't stop talking about you. You got pretty good videos man. I personally like this one; youtube.com/watch?v=aeftiMWTkW8. However, I'm sorry if this isn't you. Good job on the diagram!
    – Jason Hoff
    Apr 10 '20 at 10:54

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.

  • Thanks for the feedback, I appreciate it! I suppose the discount info would be better off in the AutoMakers table. The dedicated table is unnecessary.
    – Mayhem
    Apr 10 '20 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.