Database Design For Realty Website Review



  • I am designing a real estate website and wanted feedback from some professionals about the database schema that I came up with.

    I created a property table that would have each house. Each house can be listed for sale multiple times over the years, which be under the property_listings table.

    I created the database diagram over at dbdiagram.io so you can view it here: https://dbdiagram.io/d/603fc91ffcdcb6230b226f4a

    I'm wondering if anything looks like a miss or if I'm doing anything wrong. I don't typically design databases. Thank you in advance for the feedback.

    enter image description here



  • The structure seems reasonable. The datatypes have some issues.

    • "2-1/2 bathrooms" in an INT? Maybe DECIMAL(3,1).
    • BIGINT is excessive for most cases.
    • INT won't work for latitude and longitude; consider FLOAT as being quite adequate for buildings.
    • What will the "slugs" be used for?
    • Location -- Some things (eg "city") are normalized; other things are not. Think about what the queries will be searching on; this should be considered when laying out the schema.
    • Is there an "Amenities" table? It might be better to simply spell out the amenity [which, I think has only one 'm'], not having to go through an "id".

    How about

    • "Distance from mass transit"
    • You have moved some amenities (bedrooms and bathrooms) into the main table; I suggest you move a few of the more popular "requirements" into the main table. This will speed up searching.

    I hope you will "go green" and have amenities like "solar panels" "insulated walls", "double/triple insulated windows", "xeriscaped lawn", "height above sea level".

    More

    • Why are property and property listings` separate?
    • For nested attributes, rethink the structure: community > city > postal_code > province. However, all of them might be used for searching, so they might all be in property.
    • Consider a SET of up to 64 common amenities -- for things that usually occur either zero or one time. That takes very little space (in property_listing)
    • Consider a FULLTEXT index on a TEXT column for collecting miscellany (like "windmill, storm cellar, silo in backyard, view of Rockies")
    • Consider a single location table that has all the community, city, postal_code, province columns, but not the specific address and lat/lng. It cuts back on the number of tables, but requires redundant typing.



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2