11-11-2016, 11:51 AM
1470159585-SRSEstateRelationalDatabaseSystem.pdf (Size: 1.58 MB / Downloads: 12)
PROBLEM DESCRIPTION
Problem Statement
Since 2008, there has been substantial growth in residential apartment and
communal living properties, like dormitories, senior living centers, extended-stay
residences, and others management properties. One of the largest challenges in
maintaining operating as a landlord or property manager is balancing the various
dynamics that occur daily regarding new residents, requests for repairs, and
balancing payments and invoices. A professional system that integrates the
important information like lease obligations, resident details, work requests,
payments from tenants and to suppliers is a requirement in order to maintain the
property, operate efficiently, and ensure resident’s satisfaction. Our company,
Pro-Manage Consulting, Inc. has designed a system that can help a property
manager maintain their community in a centralized system.
Organizational Description
Company Overview
Pro-Manage Consulting, Inc. is a software development company that designs and
implements management systems for single and multi-unit residential property
management companies and was founded by four students at the University of
Texas at Dallas.
Client Overview
In this example, the client, University Village Apartments, located on the campus
of the University of Texas at Dallas has installed the Pro-Manage Apartment
Management System. With this system, the property management team will be
able to insert new resident information, create leases for apartments, accept
payments for rent and track payments to suppliers.
Employees
Although in a real-world application, all employees would need to be documented
into the Pro-Manage system, in this example, we have used 4 employees as
maintenance workers who help repair issues submitted on a customer’s
workorder.
Units
In this example, University Village has 25 units, ranging in size square footage size,
from 650 sqft. to 1090 sqft. and number of bedrooms and bathrooms, with
between 1 and 2 bedrooms, and 1 or 2 bathrooms, depending on the unit size.
Residents
Although most apartment complexes would prefer to have a very high occupancy
rate, due to operating without a comprehensive management system, University
Village has suffered a significant move-out rate, resulting in only 20 residents
currently holding leases within the complex.
Workorders
One of the advantages to tenants in an apartment is that they can call the
apartment’s management office and place workorder requests with the staff of
the apartment complex to have repairs made. One of the most important
functions that this system performs is to allow the complex to track the status,
estimated times to complete and estimated and actual costs related the
requested task.
Suppliers
In order to resolve the workorders that might be placed with the apartment staff,
the complex must have specific suppliers in order to provide the needed
replacement items that meet their needs, either in quality, style, or cost, such as
light bulbs or patches of carpet.
Leases
The backbone of every residential community is effective management of leases,
from keeping records on current contracts to knowing when new units might
become available. The lease outlines the expectations of both the resident and
the management company, including the agreed amount of rent due each month
and security deposits paid. Effective reporting on the lease element of the
apartment’s operations can spell the difference between successful management
and a failing community.
SCOPE OF DATABASE
WorkOrder
This project began as a focus on how to manage workorder requests from tenants
in a managed property, however as we continued to develop this application, we
found that there were many more items that were critical to the operations of a
property management service, and expanded our scope to include these items.
However, WorkOrder was the foundation around which this project was
developed. This table tracks each work order request through a WorkOrderID,
which is the primary key. The UnitNo is a foreign key from the Units table, as
each workorder must have a Unit number attached to it. Next the WOType is
based on the JobType coming from the JobType table. WODate accepts a date
that the work order was created, so that a starting point can be determined when
the issue was reported. Priority is an descriptor ranging from “Low, Medium,
High, Urgent”. ProblemDescription is a field that permits a note that provides
more specific details about the problem. CompletionDate identifies the date that
the workorder was completed. StartDate is the date that the actual work started
on the issue, different from the WODate as there may be a delay between when
the workorder was submitted and the activity actually began. Finally,
PermissionToEnter is an attribute that identifies whether the apartment’s
employees may enter the premise to complete the workorder without the
presence of the tenant. If a tenant does not give permission, appointment details
are noted in the ProblemDescription field so that the task may be completed in a
timely manner to the tenant’s satisfaction.
Job Type
This table contains a two attributes, JobType.as the primary key, and Estimated
Hours as an attribute. The JobType is an item like “Change Light bulb” and has an
expected amount of time that would be required to complete the associated task,
such as “5 minutes.” This permits the property manager to know how much time
each workorder might take.
Units
The Units table is built around UnitNo as the primary key, as each apartment
complex may only have a single location labeled as “Unit #A1” for example. In
this table, attributes like Area_SqFt, Bedroom, Bathroom, and Garage indicate the
unit size (in square footage), number of bedroom, number of bathrooms, and
presence of an attached garage, respectively. Also, a Notes field is present to
allow any special notes about the specific unit to be documented, such as a
sunroom or patio.
Lease
As this project has developed, the Lease table has become critical to the overall
success of this project. This table uses a unique “LeaseID” as the primary key to
track each individual lease maintained within the community. The ResidentID and
UnitNo are foreign keys from the Resident and Unit tables respectively, outlining
who is associated with the lease and which unit it is applicable to. Other details,
such as LeaseDate, LeaseStartDate, and LeaseEndDate may be documented to
show when the lease was drafted and the effective start and end dates. The
LeaseTerm attribute provides the expected duration of the lease and is present
because some leases may need to be terminated early due to unforeseen
circumstances between either the tenant or property. MonthlyRent,
SecurityDeposit, and PetDeposit are all attributes that are available to indicate
what the agreed monthly rent will be, as well as the presence of any deposits,
either for security towards potential damages, or for pets, and the expected costs
associated with turning over a unit to prepare for new tenants.
Resident
As foreign key to the Lease table, the ResidentID is the primary key of the
Resident table. This item identifies each resident that is listed on the lease.
However this is slightly different from an occupant. The Resident is the primary
individual on the lease, with others listed in a subordinate position to the
resident. This table provides information captured from the Occupants table
about the Resident, including the name through the ResidentFirstName and
ResidentLastName fields, contact information through the ResidentEmailAddress,
Pro-Manage Apartment Management System – MIS6326 – Y. Ryu - Summer 2014
8
ResidentMobileNumber and ResidentHomeNumber. The Attachment field on the
Resident table permits the presence of a photograph, scanned image of a stateissued
ID, or other image to be included in the resident’s record. Finally a Notes
field is provided so that any additional details maybe documented about the
specific resident and their group.
Occupants
The Occupants table uses a primary key developed from both a unique ResID and
a unique MemberNo. The ResID, which provides the primary key for the Resident
table, is used to identify a group of residents that might be within a unit. The
MemberNo is used to identify the individual occupants that might be residing
within a residence. Only basic name information is needed, captured through the
OccupantFirstName and OccupantLastName fields. This table is used to capture
information about all persons residing within the community, including those that
might not be signors of the lease, such as a minor child, or that might be in a
subordinate position on the lease, such as a spouse or roommate. Each group of
residents will have a unique ResID, such as 005, and then a MemberID based on
their position on the lease, with the primary Resident having the “01” identifier,
and others having a subsequent number based on their position, such as “02”,
“03”, “04”, and so on.
Lease_Payment
In order for a managed property to remain financially viable, it must track every
dollar that is owed to it through it’s lease agreements. The Lease_Payment table
tracks each payment made with a unique PaymentID, which serves as the primary
key for this table. Each payment made will be attributed to a specific LeaseID
from the Lease table, and is used to match the payment to the appropriate payee
and lease. Other details, including payment type and payment category are
foreign keys, and used to correctly match each payment to the charge needing
remuneration. Other fields included are PaymentDate, PaymentAmount,
ReferenceNo, and PaymentDueDate provide additional details that are captured
referencing the Payment Date, amount paid, transaction reference number (such
as through a credit card transaction), and due date for payment respectively