Designing the Data Model for a Property Marketplace

Get the property/listing split right before anything else

If you are at the whiteboard drawing an ERD for a property marketplace, the decision that determines everything downstream is whether property and listing are one table or two. Make them two. A property is the physical asset (its location, floor area, bedroom count, year built). A listing is a time-bound marketplace offer against that property (a price, a status, an agent, a set of photos). Collapse them into one row and you will rebuild the schema inside a year, usually right when inventory and traffic are growing and a migration is most expensive.

We learned this operating Lamudi, a multi-country listings marketplace, and again building LISTD. The generic real estate database schema tutorials that rank for this query treat property and listing as interchangeable because they assume a single-property-per-listing MLS world. Real marketplaces are not that clean. The same unit gets listed by three agents at three prices. A resale flat and a pre-selling tower have to coexist in one system. Prices change and buyers want to see the history. None of that models cleanly on a merged table.

Do you even need a custom data model, or can you buy one?

The honest question is not whether to model your data (a real marketplace forces that) but how much of it to build now and how much to buy. Three answers, in order of cost.

Buy the commodity. Media storage, a search engine, a maps provider, auth, and analytics are solved problems. Do not hand-roll them. Build the core. The property, listing, and project shape encodes how your specific marketplace behaves, and no vendor gets it right for you. An off-the-shelf real estate CRM or an MLS-derived schema is built for a single brokerage in a single-property-per-listing world with clean US-style geography. The moment you need multiple agents on one property, developer inventory, your own search relevance, or monetization tied to your data, you inherit assumptions you cannot unwind. Defer the depth. The full 32-table lifecycle schema (offers, contracts, commissions, taxes, valuations, school districts) and the project and unit hierarchy are real eventually, but building them before you carry the inventory that needs them is how schema design becomes a six-month project that ships nothing.

What this actually costs you is not the initial build. The minimal core is small and takes days, not months. The expense is the wrong-shape rework: discovering the constraint after a million listings and paying customers sit on the old model. Spend your effort getting the property and listing split right and leaving the seams for the rest.

The core entities you actually need

Start narrow. A property marketplace data model does not need the fully normalized real estate schema on day one. The minimal core that survives contact with production:

• property: the deduplicated physical asset (coordinates, address, specs, property type).
• listing: a marketplace offer against a property (price, status, listing type of sale or rent, timestamps).
• agent and agency: who is selling, and the multi-tenant agent and agency relationship between them.
• developer, project, unit_type, unit: the new-development hierarchy (optional, covered below).
• listing_photo and related media: kept in their own table rather than as columns on the listing.
• price_history and status_history: append-only logs, not columns you overwrite.

Two relationships trip teams up here. First, agents and agencies are multi-tenant: one agency has many agents, and an agent can move between agencies or list across several over time. Model the agent-to-listing assignment (an in_charge or assignment record) separately from ownership, because who is responsible for a listing is not who owns the property, and it changes. Lead routing and monetization later both depend on that separation. Second, resist putting many-to-many attributes like amenities as columns on the property. A junction table (property_amenity) keeps the property row stable as the amenity vocabulary grows.

Everything else (leads, featured flags, subscriptions, saved searches) hangs off this core later without forcing a rewrite. If you are still deciding the surrounding architecture, this model is one piece of a larger set of choices we worked through in building a two-sided property marketplace.

Modeling new-development inventory without breaking resale

The moment your marketplace carries pre-selling inventory, the resale-only property and listing pair stops being enough. A new development is not one property with one price. It is a developer, running a project, that contains several unit types (a one-bedroom line, a two-bedroom line, a penthouse), each with its own base price and floor plan, and each unit type contains many individually sellable units.

Model that as an explicit developer, project, unit type, and unit hierarchy sitting above property. Each unit can still produce a listing, so search, pricing, and status logic stay uniform across resale and new-build. The rule that keeps this clean: make the project and unit layer optional, not mandatory. A resale listing points straight at a single property and skips the hierarchy entirely. Force every listing through a project and you have punished your simplest, highest-volume case to serve your most complex one.

This is also where absorption and sales velocity live: how many units in a unit type are sold, reserved, or available over time. Track it as counts and events on the unit layer, not as a mutable number you decrement on the project row, or you will lose the history the day someone asks how fast a tower actually sold.

Location and geo-search in messy real-world markets

Model location as a hierarchy: property, neighborhood, city, region, with lat and lng on the property for map queries. This geospatial data model is what powers localized filtering, "near me" search, and the map. The part the tutorials skip is that real administrative boundaries are messy.

Western schemas quietly assume a clean city, state, and zip structure. Southeast Asian markets do not follow it. The Philippines nests a barangay inside a city inside a province, postal codes are unreliable, and neighborhood names overlap and shift. If your location hierarchy hardcodes a US-shaped assumption, you will fight it in every new market. Keep the hierarchy flexible (a self-referential location tree, or explicit typed levels you can extend) and keep the raw lat and lng authoritative so search never depends on clean administrative data. The downstream search and map layers consume these choices directly, so treat the location model, search, and map as one system rather than three.

Duplicate listings and multiple agents on one property

On an aggregator, the same physical unit routinely appears as several listings from several agents at conflicting prices. If property and listing are separate, this is trivial: property is the canonical, deduplicated entity and listing is a one-to-many child pointing at it. Reconciliation (which listing is the active one, whose price to show, when to flag a duplicate) becomes business logic and a trust problem rather than a schema problem.

If you merged the two tables, you have nowhere to attach that logic, and every deduplication or stale-listing feature turns into a migration. The schema is what makes the trust work tractable, which is the direct link between this model and trust and safety on a listings marketplace.

Price and status history: append, never overwrite

Version price and status from day one. Both belong in append-only tables (price_history, status_history) or a single event log, never as a column someone runs an UPDATE against. This is the cheapest thing to add up front and one of the most expensive to retrofit, because once the history is gone it is gone.

You need it for three reasons: buyer-facing price change tracking (a real feature buyers trust), trust and safety (detecting stale, manipulated, or bait pricing), and analytics (absorption, time on market, price trends). All three are impossible if the current price is the only price you ever stored.

Core tables plus extensions, or schemaless JSON

Property attributes vary widely by type. A condo has bedrooms and a floor level, a parcel of land has zoning and frontage, a commercial unit has a different set again. You have two honest ways to absorb that variation without a rigid single wide table:

• Core plus type-specific extension tables. A shared property core, plus property_residential, property_land, and property_commercial for the type-specific fields. Adding a new property type is a new extension table, not an ALTER on a hundred-column monster.
• A JSON attributes column on the core for the long tail of rarely queried fields, keeping the columns you actually filter on as real, indexed columns.

How to choose: most teams end up combining both, and both beat the two extremes. A rigid wide table needs a migration for every new field. A fully schemaless free-for-all guarantees nothing and makes every query defensive. Put the fields you filter and sort on in real columns, push the variable tail into extensions or JSON. This is the normalization vs denormalization judgment call, and it is genuinely a judgment call, not a rule.

One more piece of leverage: align your listing fields to schema.org's RealEstateListing vocabulary (price, availability, sale versus lease). Do that and your structured-data output for SEO becomes a thin serialization of your model rather than a second, drifting source of truth.

Postgres or MongoDB, and why most marketplaces go hybrid

The SQL versus NoSQL debate for a listings and agents data model usually resolves to "both, for different jobs."

Keep one system of record. Postgres holds the authoritative property, listing, agent, and history tables where integrity and joins matter. A document or search engine (Elasticsearch, or MongoDB) holds a derived, denormalized copy tuned for the query-heavy search experience. The discipline is that the search layer is rebuildable from the relational core and never authoritative on its own. Let both be authoritative and you have signed up for reconciliation bugs forever.

Designing for the migration you have not thought of yet

The honest goal is not a perfect schema. It is a schema whose mistakes are cheap to fix. Every decision above is really about keeping optionality: property separate from listing so duplicates and history have somewhere to live, the project and unit layer optional so resale stays simple, history append-only so you never destroy data you will want later, a flexible location tree so a new market does not mean a migration.

The failure modes are predictable, which is what makes them avoidable. Stale listing status that never reconciles with what the agent actually sold. Price history erased by an UPDATE. A geo-hierarchy that assumes zip codes in a market that has none. Each one traces back to a shortcut in the core shape. We have gotten this wrong once and fixed it in a live, revenue-generating marketplace, which is a very different teacher than a clean-room ERD. If you are staring at a model that is already fighting you, the real question is whether you are looking at a refactor or a rebuild, which we worked through in rebuild or refactor when a marketplace MVP hits its ceiling.