Database Design & Management

Executive Summary

Real estate is the most globally recognized industry. With the increasing demand for professional customer service to facilitate the transactions, real estate brokerage became one of the hottest businesses projected to solid growth post-pandemic. A seamlessly integrated database management system can facilitate agents in better helping clients and optimizing stakeholders’ profit. There are some pain points to operating a real estate firm and better data management will help businesses in relife those issues. To stand out from the highly competitive real estate industry, using different data to manage information and make an efficient decision is important.

One of the biggest real estate brokerages is looking to upgrade its management system helping the company better manage its agents. The company grew so quickly last year and the management found difficulty in managing their agents. The current information system cannot meet the requirements after the unexpected expansion last year. The management decided to implement a database management system not only for better managing the company employee but also to facilitate future expansion. The company is looking for a 30% revenue increase next year. The business is taking a small portion of the commission from each transaction, and the business relies on the agents who are independent contractors for the brokerage. The company needs to better facilitate its agents in helping clients to increase revenue. Agents are the people who really keep the business going. To better keep track of the business activities from each agent and assist them in helping clients is more and more difficult for the firm with large scales in size. Besides managing the agents, facilitating agents in managing the client’s relationship is critical to increasing closing rate and generating revenue. Understanding their client’s needs and better matching the right property for them is a fundamental element to the decided closing rate. The business relies on the client relationship, using data to analyze clients’ needs and knowing the customers better would help the firm in future growth and expansion. Properties’ listing is the inventory for a company, keeping track of each listing becomes more complicated when the company has more inventories, and adjusting the price for each property will increase the turnover rate as well as bring more profits for stakeholders.

An OLAP database management system designed for real estate brokerage can help with the problems mentioned above by better storing, managing, and analyzing data from different perspectives. The system we are designing is helping the brokerage firm in better managing agents’ activities, and calculating commission for accounting purposes. From agents’ perspective, the system is helping them to better track their leads and better assist their clients in increasing customer engagement by analyzing clients' activities. The agents can easily update the properties information to the databases, and the system analyzes the property attributes to the system and provides estimated listing price for facilitating pricing strategy. The new system also helps agents track their marketing expenditures and calculate the net income for each transaction.

Logical Model

Assumptions about Business Rules

1. Employee_Client: Each client must serve at least one employee from the brokerage, and the agent might co-broker with each other to serve the same client at the same time. But not every agent is mandatory to have the client. Clients can be potential clients who have not started business with the company. We do not have much information. Therefore, only the client’s first name and id is mandatory in the client entity.

2. Estate_Transaciton: The client might use multiple transactions to make the purchase or rent a property, so the one property can have multiple transactions.

3. Transaction_Payment: Each payment is mandatory belongs to a transaction, and each transaction must have at least one payment. This part is mandatory based on every existing purchase meaning since each successfully traded property must correspond to a transaction record; If the real estate does not have corresponding transaction records, then the transaction data related to them will not exist either.

4. Employee_Listing: Not every employee is mandatory to have a listing, but listing is mandatory to at least one agent.

5. Lisitng_Cost: The agents need to advertise the properties to sell/ rent out the property, which might generate a cost or more cost, but it is not mandatory. If there is a cost generated, it must according to one listing.

6. Employee_Account: Each agent has to have an account and each account belongs to one agent. This part is mandatory since every employee should have an account to receive a commission.

7. Account_Commission: The account can receive many commissions, but the new agent might not close any deal yet, so it is not mandatory. But each commission has to belong to an account. Plus, in this part, the system will record related information about the commission, like the date and the amount of this commission.

8. Contact_client: The system can also help agents to keep track of their leads. One agent can have multiple contacts, but even if the same client can be reached out by different agents, one contact only belongs to one agent with a different contact id.