SocietySync ERP - A Normalized Relational Database Architecture for Residential Management

Abstract

Many residential apartments from today are still using paper/pencil systems, instead of an automated system for recording new tenants, maintaining financial records, tracking customer complaints, and communicating with tenants. As expected, utilizing these systems creates both inefficiencies and inaccuracies within the systems. SocietySyncERP was designed and developed to assist in eliminating these inefficiencies and inaccuracies with database automation. SocietySyncERP uses a PostgreSQL database that consists of 11 interrelated tables and three end-user types: Administrators, Property Owners, and Tenants. The database design for SocietySyncERP has been normalized to the third normal form (3NF), supports the requirements for ACID Transactions, has included the use of bcrypt for password hashing, and has implemented measures to detect and prevent SQL injection attacks to date. Currently, the SocietySyncERP database has approximately twenty-five features, such as billing management, complaint tracking, visitor logs, polling, etc. The SocietySyncERP database has been tested by 44 users and has demonstrated acceptable performance on queries and data integrity, with query times averaging between 2 and 20 milliseconds. SocietySyncERP adheres to a standard three-tier architectural design to provide the system with the ability to scale to meet future needs. The database theories of relationship algebra, normalization, and complexity theory form the foundation for SocietySyncERP databases and represent a direct relationship between the conceptual theories of database design taught in the classroom and the actual needs of contemporary apartment communities.