Entity Relationship Diagram

CORE DATA

The core data needed for the PROPERTY MANAGEMENT system and its structure is shown below. The key entities here are: Facility and Person. The Facility table defines the property object and the Person table defines persons, juridical or natural, which exercise ownership rights over the property. All other tables defined are ancillary to these two tables.

Facility Table

The Facility table describes a piece of real property. Each record in this table corresponds to a real property which is uniquely identified by Property ID. Each real property is associated to a person who may be its owner, administrator, or resident using the Role Code field in the Role Register table. A real property may be associated to multiple persons.

The status of  a facility may be any of the following: common (for common areas), private (for the private areas) and unavailable (for common areas that are closed or cannot be used for whatever reason).

Maintenance

There should be a module that allows for the maintenance of data in this table. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

This table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Person Table

The Person table defines a person, which could be natural or juridical (Person Type). The juridical type of person could be a group, which will consist of individual Person units. If the Person ID and Group ID are the same, the Person is a basic Person unit. If the Person ID is different from the Group ID, the Person is a component Person unit of a Person group.

Maintenance

There should be a module that allows for the maintenance of data in this table. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

This table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Role Register Table

The Role Register Table identifies the current relationship of a Person to a Facility. This relationship can be that of an owner, administrator, or resident and is defined by a period, with a start date and an optional end date.

It is important to note that the owner, administrator or resident could be a Person group. In case of an unincorporated Person group such as a family unit, the Person group will be recorded with the attributes of the head of the family, and a separate record for such head of the family will have to be created as an individual Person unit.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Contact Info Table

The Contact Info Table is the container for the contact information of a Person. Since there are multiple contact information each person can have, there should be an indicator Primary Contact for the particular kind of Contact Type. Setting a Primary Contact should be subject to integrity check, as there should only be one primary contact for every Contact Type. A Contact Type could be mobile phone, landline phone, email, or physical address. The details for a physical address will be stored in another table, Address Table, while details for other contact types will be stored in the Contact Details field.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Address Table

The Address Table defines the address information for a Person or a Facility.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

ANCILLARY DATA 

In addition to the core data describing Facility and Person, the envisioned system includes a booking system for common areas in a condominium corporation and a ticketing system for service requests. For these purposes, the Booking and Service Request tables are key.

Booking Table

Logic for the Booking Table is fairly straightforward. Each booking links to a Facility, and each Facility will have a Booking Rule. The Booking Rule table contains three Boolean fields to indicate whether the Facility requires payment to be booked, whether it allows tentative booking, and whether it accepts wait list booking.

If a Facility requires payment for booking to be finalized, the Booking table will require the Payment Reference to be populated before Booking Status can be made Final. If tentative booking is allowed, a Booking Status of Tentative will be valid. The business will have to determine how a tentative booking will be managed, but systemically, a only a final booking can be made and override a tentative one. A booking cannot be made when there is already an existing booking (final or tentative) unless Wait listing is allowed, in which case the Wait list number automatically increments. At this point, the Wait list number, aside from the automatic increment, will serve as mere reference and the business will have to manage which Wait listed booking is subsequently given prioritization.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date. The first two columns mentioned can be substituted with Booked By and Booked Date.

Booking Rule Table

An entry in the Booking Rule table is required for the creation of Facility type corresponding to common areas (see Status under Facility table). It contains three Boolean fields to indicate whether the Facility requires payment to be booked, whether it allows tentative booking, and whether it accepts wait list booking. The value of these fields determine the business logic embedded in the Booking table discussed above.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Booking Status Table

The Booking Status table defines the status of a Booking. This table is pre-populated with at least the following status: confirmed or final, tentative and waitlisted. Other statuses can be added as the need arises.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Service Request Table

The Service Request table is the base table for service request ticketing. It contains all tickets, one is created for every request. The Request Notes will be a free text field that will contain details of the nature of the request. Initially, the Last Note ID and the Ticket Type Code should be null to indicate that the ticket is for first level triage. There should also be a default ticket status.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Ticket Status Table

The Ticket Status table will define the status of the ticket. It should have, at the minimum, the following entries: submitted (which is the default), in progress, on hold and completed.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Ticket Type Table

The Ticket Type table will define a categorization for the tickets in use. This could pertain to the functional area of the business which is responsible for the ticket (i.e., Physical Plant, Front Desk, Administration, etc.), it could pertain to the nature of the ticket (Entry Instructions, Incident Report, etc.) or any categorization useful for the business.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Service Note Table

The Service Note table contains the step-wise detail in the workflow associated with a Service Request. For every assignment of a ticket to a Person, a service note should be created. It will contain information on the current status of the service request. To allow for full audit trail, a service note must link back to its predecessor note and to the note that follows it.

Maintenance

There should be a module that allows for the maintenance of data in these tables. This should include creation of new record, modification of existing record, and deletion of a record. Maintenance authority will depend on the Role Code, subject to alignment to business requirements.

Audit

These table, in addition to the fields already shown, should have the following columns: Created By, Creation Date, Last Modified By, Last Modified Date.

Note: The Entity Relationship Diagram presented above is baseline draft which is sufficient for a minimum viable product but which can be improved and optimized as part of the product road map.