Please help me verify my attached ER diagram.
I am building a Work Order module and so far have defined the following entities:
1) USERS � Each USER can access one or more COMPANIES
2) COMPANY - Each COMPANY can have one or more PROPERTIES
3) PROPERTY � Each PROPERTY has multiple apartment UNITS
4) UNIT � Each apartment UNIT can have multiple rental APPLICATIONS
5) APPLICATION - Each rental APPLICATION can results in 1 LEASE
6) LEASE � Each LEASE have 1 TENANT (occupants listed separately)
7) TENANT � Each TENANT can have multiple TICKETS to report problems
8) TICKET � Each problem TICKET can have multiple WORKORDER
9) WORKORDER � Signed off upon repair job completion
I have defined a 1:n Identifying Relationship for all entities except LEASE and TENANT (1:1 Identifying Relationship), this resulted in a 7 levels deep cascading reference for the last entity WORKORDER. So by the time I got to the last table WORKORDER, it wound up with the following PKs & FK. With the exception of the very 1st key, everything else is generated by MySQL Workbench:
Primary Keys Fields (all are INT, Auto increment)
-------------------------------------------------
ID (WORKORDER_ID) - The only column I specified
TICKET_ID
TICKET_TENANT_ID
TICKET_TENANT_LEASE_ID
TICKET_TENANT_LEASE_APPLICATION_ID
TICKET_TENANT_LEASE_APPLICATION_UNIT_ID
TICKET_TENANT_LEASE_APPLICATION_UNIT_PROPERTY_ID
TICKET_TENANT_LEASE_APPLICATION_UNIT_PROPERY_COMPANY_ID
Foreign Key
-----------
WORKORDER_TICKET
This seems normalized to me but since I am no expert in normalization it worries me a lot because it looks a bid crazy. I'd greatly appreciated if some experts here can comment on the correctness of my ERD design and also comment on performance and other future implications as it pertains to MySQL and Alpha Five.
I am building a Work Order module and so far have defined the following entities:
1) USERS � Each USER can access one or more COMPANIES
2) COMPANY - Each COMPANY can have one or more PROPERTIES
3) PROPERTY � Each PROPERTY has multiple apartment UNITS
4) UNIT � Each apartment UNIT can have multiple rental APPLICATIONS
5) APPLICATION - Each rental APPLICATION can results in 1 LEASE
6) LEASE � Each LEASE have 1 TENANT (occupants listed separately)
7) TENANT � Each TENANT can have multiple TICKETS to report problems
8) TICKET � Each problem TICKET can have multiple WORKORDER
9) WORKORDER � Signed off upon repair job completion
I have defined a 1:n Identifying Relationship for all entities except LEASE and TENANT (1:1 Identifying Relationship), this resulted in a 7 levels deep cascading reference for the last entity WORKORDER. So by the time I got to the last table WORKORDER, it wound up with the following PKs & FK. With the exception of the very 1st key, everything else is generated by MySQL Workbench:
Primary Keys Fields (all are INT, Auto increment)
-------------------------------------------------
ID (WORKORDER_ID) - The only column I specified
TICKET_ID
TICKET_TENANT_ID
TICKET_TENANT_LEASE_ID
TICKET_TENANT_LEASE_APPLICATION_ID
TICKET_TENANT_LEASE_APPLICATION_UNIT_ID
TICKET_TENANT_LEASE_APPLICATION_UNIT_PROPERTY_ID
TICKET_TENANT_LEASE_APPLICATION_UNIT_PROPERY_COMPANY_ID
Foreign Key
-----------
WORKORDER_TICKET
This seems normalized to me but since I am no expert in normalization it worries me a lot because it looks a bid crazy. I'd greatly appreciated if some experts here can comment on the correctness of my ERD design and also comment on performance and other future implications as it pertains to MySQL and Alpha Five.
Comment