# Local Real Estate

## Schema Overview

This database underpins a real estate application, managing properties, agents, firms, listings, and buyer preferences. It facilitates core operations from property management to agent-buyer matching.

## Table Summary

| Table Name | Primary Responsibility |
| :--------- | :--------------------- |
| `Property` | Defines the foundational details of all real estate holdings. |
| `House` | Stores specific attributes for residential properties. |
| `BusinessProperty` | Holds details unique to commercial real estate. |
| `Firm` | Manages real estate agencies. |
| `Agent` | Records information about real estate agents. |
| `Listing` | Links properties to agents for active sales. |
| `Buyer` | Captures detailed preferences for prospective property buyers. |
| `Work_With` | Establishes relationships between buyers and agents. |

## `Property` Table

This table serves as the supertype for all property listings, establishing a common base for houses and business properties. It is central to property identification and ownership tracking.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `address` | VARCHAR(50) | Unique identifier for this entity. Used for all lookups and relationships. |
| `ownerName` | VARCHAR(30) (Optional) | The name of the property's current owner. |
| `price` | INT (Optional) | The listed price of the property. Map to `java.lang.Integer` or `java.math.BigDecimal` for robust calculations. |

## `House` Table

Contains attributes specific to residential properties, extending the general `Property` information. This table enables detailed filtering and searching for house listings.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `address` | VARCHAR(50) | Establishes a relationship with `Property.address`. Essential for data integrity and joins. |
| `ownerName` | VARCHAR(30) (Optional) | The name of the house's current owner. Inherited from `Property`. |
| `price` | INT (Optional) | The listed price of the house. Inherited from `Property`. Map to `java.lang.Integer` or `java.math.BigDecimal` for robust calculations. |
| `bedrooms` | INT (Optional) | Number of bedrooms in the house. |
| `bathrooms` | INT (Optional) | Number of bathrooms in the house. |
| `size` | INT (Optional) | Square footage or similar size metric of the house. |

## `BusinessProperty` Table

Holds characteristics unique to commercial or business properties, differentiating them from residential listings.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `address` | VARCHAR(50) | Establishes a relationship with `Property.address`. Essential for data integrity and joins. |
| `ownerName` | VARCHAR(30) (Optional) | The name of the business property's current owner. Inherited from `Property`. |
| `price` | INT (Optional) | The listed price of the business property. Inherited from `Property`. Map to `java.lang.Integer` or `java.math.BigDecimal` for robust calculations. |
| `type` | CHAR(20) (Optional) | Categorization of the business property (e.g., 'office space', 'retail'). Represents an enumerated value. |
| `size` | INT (Optional) | Square footage or similar size metric of the business property. |

## `Firm` Table

Manages records for real estate agencies, providing organizational context for agents and listings.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `id` | INT | Unique identifier for this entity. Used for all lookups and relationships. |
| `name` | VARCHAR(30) (Optional) | The name of the real estate firm. |
| `address` | VARCHAR(50) (Optional) | The physical address of the firm. |

## `Agent` Table

Stores details for individual real estate agents, including their affiliation with firms.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `agentId` | INT | Unique identifier for this entity. Used for all lookups and relationships. |
| `name` | VARCHAR(30) (Optional) | The agent's full name. |
| `phone` | CHAR(12) (Optional) | The agent's contact phone number. |
| `firmId` | INT | Establishes a relationship with `Firm.id`. Essential for data integrity and joins, indicating the agent's employer. |
| `dateStarted` | DATE (Optional) | The date the agent started working at the firm. Map to `java.time.LocalDate` for modern date handling. |

## `Listing` Table

Links properties with the agents responsible for their sale, including listing-specific details.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `address` | VARCHAR(50) (Optional) | Establishes a relationship with `Property.address`. Essential for data integrity and joins. |
| `agentId` | INT (Optional) | Establishes a relationship with `Agent.agentId`. Essential for data integrity and joins, indicating the listing agent. |
| `mlsNumber` | INT | Unique identifier for this entity. Used for all lookups and relationships. This is the Multiple Listing Service number. |
| `dataListed` | DATE (Optional) | The date the property was listed. Map to `java.time.LocalDate` for modern date handling. |

## `Buyer` Table

Captures comprehensive search criteria and personal information for potential property buyers.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `id` | INT | Unique identifier for this entity. Used for all lookups and relationships. |
| `name` | VARCHAR(30) (Optional) | The buyer's full name. |
| `phone` | CHAR(12) (Optional) | The buyer's contact phone number. |
| `propertyType` | CHAR(20) (Optional) | The preferred type of property (e.g., 'house', 'not applied'). Represents an enumerated value. |
| `bedrooms` | INT (Optional) | The minimum number of bedrooms desired for a house. |
| `bathrooms` | INT (Optional) | The minimum number of bathrooms desired for a house. |
| `businessPropertyType` | CHAR(20) (Optional) | The preferred type of business property (e.g., 'office space', 'not applied'). Represents an enumerated value. |
| `minimumPreferredPrice` | INT (Optional) | The lowest acceptable price for a property. Map to `java.lang.Integer` or `java.math.BigDecimal` for robust calculations. |
| `maximumPreferredPrice` | INT (Optional) | The highest acceptable price for a property. Map to `java.lang.Integer` or `java.math.BigDecimal` for robust calculations. |

## `Work_With` Table

A junction table establishing many-to-many relationships between buyers and agents, enabling tracking of client assignments.

| Column Name | Data Type | Description |
| :---------- | :-------- | :---------- |
| `buyerId` | INT (Optional) | Establishes a relationship with `Buyer.id`. Essential for data integrity and joins. |
| `agentId` | INT (Optional) | Establishes a relationship with `Agent.agentId`. Essential for data integrity and joins. |