0

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

Improve this question
3
  • Can you verify that link?
    – Gerard H. Pille
    Jan 20 at 6:24
  • images.property_listing_id, property.community_id -> bigint. Prices, taxes and fees as int, suggest decimal maybe. otherwise generally looks ok.
    – danblack
    Jan 20 at 7:44
  • Thank you for your input.
    – Elise Crane
    Jan 24 at 7:39

1 Answer 1

Reset to default
1

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.
Improve this answer
4
  • Hi @RickJames, thank you for the response. For bathrooms would you suggest using varchar? I will update the bigint fields to int as suggested and switch the latitude to FLOAT. The slug field was going to be for the friendly URL string but maybe that's not needed since it will match the city/community anyway? For database normalization are you referring to the province field? The site will only be for one province. So, have one table for all amenities and just use the ID to connect them? I just wanted to reduce duplicate amenities. I will add other popular amenities to the main table as well.
    – Elise Crane
    Jan 24 at 7:37
  • @EliseCrane - I added some More ideas. (I have not implemented such a database, so I don't have "experience". Still, I have pondered the issues a few times.) While moving columns around, try to write the SELECTs that you will need. That will help you understand the pros and cons of normalization, fulltext, etc.
    – Rick James
    Jan 24 at 17:17
  • Thanks, @RickJames, for the update. Property is separate from property listings because the property can be listed for sale again, and photos, renovations, etc. can change the details of the listing each time. The address and other details should not change which are in "property". I have community and city separate to help with search queries and not duplicate data, but maybe that approach isn't ideal. Your idea about amenities is good, I will do that. Could you refer me to any good reading material that's straightforward that will help me get the perfect design? I only want to do this once.
    – Elise Crane
    Jan 30 at 6:05
  • @EliseCrane - I have encountered any good reading; these are mostly ideas that I came up long ago. Don't plan on "doing it only once"; instead do plan on revisiting the design after a few months. Be willing to take a copy of the schema and play around with the normalization / amenities / etc. You will find weak places in the design that need fixing. And new amenities will be invented almost daily. As climate change progresses, "green" things will be more important; other things will fade away.
    – Rick James
    Jan 30 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.