ERModel Current Version

Diagram

Entities

Admin

Admins create and manage building projects, define units. This entity enforces access control, ensuring only an authorised person can modify critical business data. Admin is modeled separately to maintain clear separation of concerns and support future admin-specific features.

• Candidate Keys:
  • admin_id (Primary Key) - Artificially generated identifier

• Attributes:
  • admin_id (VARCHAR(36), required) - Automatically generated unique identifier for each admin. This attribute would be the primary key.
  • name (VARCHAR(128), required) - Full name of the administrator. Must not be empty.
  • email (VARCHAR(128), required, unique) - Email address of the administrator used for system login. Must be of valid email format (contains @ symbol and domain).
  • password (VARCHAR(128), required) - Encrypted password for authentication. Stored using a hashing algorithm. Minimum 8 characters required before hashing. (Could introduce further restrictions for special characters)

Architect

The Architect entity represents architectural professionals who design buildings in the system. This entity is used for legal, marketing, and historical purposes. Architects are modeled separately to support many-to-many relationships where one architect designs multiple buildings and buildings can have multiple contributing architects.

• Candidate Keys:
  • architect_id (Primary Key) - Artificially generated identifier

• Attributes:
  • architect_id (VARCHAR(36), required) - Automatically generated unique identifier for each architect. This attribute is the primary key.
  • full_name (VARCHAR(128), required) - Architect's complete name or firm name. Must not be empty.

Building

The Building entity represents a physical building projects in the system. Each building serves as a container for floors and units, and is the top layer displayed between all other buildings. Buildings are managed by admins who created them.

• Candidate Keys:
  • building_id (Primary Key) - Artificially generated identifier

• Attributes:
  • building_id (VARCHAR(36), required) - Automatically generated unique identifier for each building. This attribute is the primary key.
  • name (VARCHAR(256), required) - Descriptive name for the building. Must not be empty.
  • address (VARCHAR(512), required) - Complete address of the building including street, city, and postal code. Must not be empty.
  • description (TEXT, optional) - Detailed description of the building mainly for marketing purposes.

Floor

The Floor entity represents a level within a building, serving as an intermediary layer between buildings and units. Floors are modeled separately because they represent a distinct structural component shared by multiple units, that primarily will serve as to hold the image of the floor (3D render, floorplan).

• Candidate Keys:
  • floor_id (Primary Key) - Artificially generated identifier

• Attributes:
  • floor_id (VARCHAR(36), required) - Automatically generated unique identifier for each floor. This attribute is the primary key.
  • floor_number (INTEGER, required, unique) - The numeric level of the floor within the building. Must be unique within a building.
  • layout_image (VARCHAR(512), required) - URL or file path to the floor's image. Will be used for interactive flat selection from the image and this image will be the base on which flat images will be overlaid.

Unit

The Unit entity represents an individual space available for sale. Units are the core entity, containing all information needed for sales including pricing, availability, physical characteristics, and images.

• Candidate Keys:
  • unit_id (Primary Key) - Artificially generated identifier

• Attributes:
  • unit_id (VARCHAR(36), required) - Automatically generated unique identifier for each unit. This attribute is the primary key.
  • unit_number (VARCHAR(64), required, unique) - Presentable identifier of the unit. Must be unique within a floor.
  • room_number (INTEGER, required) - Total number of rooms in the unit. Must be a positive integer.
  • status (VARCHAR(32), required) - Current availability status of the unit. Allowed values: "Available", "Reserved", "Sold". Default: "Available".
  • price (DECIMAL(15,2), required) - Sale price of the unit in the system's base currency. Must be a positive number.
  • floor_area (DECIMAL(10,2), required) - Total floor area of the unit in square meters. Must be a positive number.
  • image (VARCHAR(512), optional) - URL or file path to the promotional image of the unit. Used for listings and thumbnails.
  • floorplan (VARCHAR(512), optional) - URL or file path to the unit's floorplan image or PDF showing room layout and dimensions.
  • vector_image (VARCHAR(512), required) - URL or file path to a vector representation of the unit for overlaying on top of the floor image for interactive flat selections within a floor.

Client

The Client entity represents potential buyers who submit inquiries or book appointments. Modeled without authentication, the system allows anyone to express interest without creating an account. Clients are automatically created from information they provide in forms.

• Candidate Keys:
  • client_id (Primary Key) - Artificially generated identifier

• Attributes:
  • client_id (VARCHAR(36), required) - Automatically generated unique identifier for each client. This attribute is the primary key.
  • name (VARCHAR(128), required) - The full name of the client as provided in inquiry or appointment forms. Must not be empty.
  • email (VARCHAR(256), required, unique) - Email address of the client for communication. Must follow valid email format.
  • phone (VARCHAR(32), required) - Phone number of the client for direct contact. Format validation varies by country. Must not be empty.

Agent

The Agent entity represents real estate agents who respond to inquiries, manage availability through timeslots, and conduct viewings. Agents are authenticated users modeled separately from Admin and Client to enforce role-based access control for the creation of timeslots.

• Candidate Keys:
  • agent_id (Primary Key) - Artificially generated identifier

• Attributes:
  • agent_id (VARCHAR(36), required) - Automatically generated unique identifier for each agent. This entity is the primary key.
  • name (VARCHAR(256), required) - Full name of the agent. Must not be empty.
  • email (VARCHAR(256), required, unique) - Email address of the agent used for system login. Must follow valid email format.
  • password (VARCHAR(256), required) - Encrypted password for authentication. Stored using a hashing algorithm. Minimum 8 characters required before hashing. (Could introduce further restrictions for special characters)

Inquiry

The Inquiry entity represents questions about units that are created by clients. Each inquiry is assigned to an agent for response, with status tracking. Inquiries serve as the initial contact for interested buyers.

• Candidate Keys:
  • inquiry_id (Primary Key) - Artificially generated identifier

• Attributes:
  • inquiry_id (VARCHAR(36), required) - Automatically generated unique identifier for each inquiry. This attribute is the primary key.
  • message (TEXT, required) - The text content of the client's questions or detailed information that they request. Must not be empty.
  • status (VARCHAR(32), required) - Current state of the inquiry. Allowed values: "New", "Replied", "Closed". Default: "New".
  • created_at (TIMESTAMP, required) - Automatically set to current timestamp when inquiry is submitted. Used for sorting.

Appointment

The Appointment entity represents scheduled property viewings linking client, unit, agent, and timeslot. Appointments are the critical from the business perspective it's the point where clients physically view the property. Status tracking manages the scheduling through completion or cancellation, needed for coordinating the viewing process.

• Candidate Keys:
  • appointment_id (Primary Key) - Artificially generated identifier

• Attributes:
  • appointment_id (VARCHAR(36), required) - Automatically generated unique identifier for each appointment. This attribute is the primary key.
  • status (VARCHAR(32), required) - Current state of the appointment. Allowed values: "Scheduled", "Completed", "Cancelled". Default: "Scheduled".

Timeslot

The Timeslot entity represents the agent availability periods for viewings. Agents create timeslots defining their availability, and clients book appointments by selecting from available slots. Each timeslot supports one appointment, with status changing from "Available" to "Booked" when used, preventing race conditions.

• Candidate Keys:
  • timeslot_id (Primary Key) - Artificially generated identifier

• Attributes:
  • timeslot_id (VARCHAR(36), required) - Automatically generated unique identifier for each timeslot. This attribute is the primary key.
  • date (DATE, required) - The calendar date for this timeslot. Format: DD-MM-YYYY. Must be today or in the future.
  • time_start (TIME, required) - Start time of the timeslot window. Format: HH:MM:SS.
  • time_end (TIME, required) - End time of the timeslot window. Format: HH:MM:SS. Must be after time_start.
  • status (VARCHAR(32), required) - Current state of the timeslot. Allowed values: "Available", "Booked". Default: "Available".

Relationships

ERModel History

• Version 01
  • Initial version