Hi.

I'd like to ask you another question. I read a lot of articles about the topic but I'm still not very sure how to deal with the following problem.

I want to design a database that contains the properties that a real estate agency offers.
The problem is - there are different types of properties - flats, shops, restaurants, hotels, fields...

All these types have some common attributes like location, price, date the property was added, agent that added it, but in the same time all of these types have specific attributes.

I can think of 3 ways to design this database.

1. Use one table for all properties.

for example:
properties (property_id, location, price, date, flat_feature_1, flat_feature_2, shop_feature_1, shop_feature_2, hotel_feature_1...)

This approach seems easy to use but it would introduce a lot of wasted storage since every record will have a lot of NULL values.

2. Use one table for the common properties and separate tables for the different types of properties (shops, hotels, fields...).

for example:
properties (property_id, location, price, date)
flats (property_id, bedrooms, baths, floor...)
shop (property_id, mall_name, equipment...)

3. Use separate tables for the different types of properties.

for example:
flats (flat_id, location, price, date, bedrooms, baths, floor...)
shops (shop_id, location, price, date, mall_name, equipment...)

---

I probably won't chose the first approach. I'd love to know what you think about 2 and 3. Do you think it's a bad idea to have identical fields in different tables like in approach 3?

Thank you very much.

It really depends on how buzy your website is expected to be. In case you are expecting a lot of both read and write transactions for the flats or let's say country houses, you better keep them in a separate table, instead of mixing them with the rest.

How about rentals and sales offered? Are you going to use a marker for sales/rentals/both properties, or you're going to keep them in separate tables?

I would go for the separate table for each property type, which table will hold both for sale and for rent. This way you will have faster searching.

Also - think of a way for removing sold entries from the database once they have been sold. This will boost search as well.

For example...you can remove entries marked sold once a week, moving them to a table called sold. This way users that want to review a sold house will find it easier in the sold database, while active entries will be querried much faster.

Regarding table columns...keep as much as possible the common specs(e.g. size, floor, price, etc. ) using identical structure in all your tables. In this way you will spend less time figuring out how to make the correct querry, without changing a lot of code.

Good luck

Thank you so very much Ryhan. I didn't expect so detailed and friendly reply. Sorry for delaying mine.

I think I would go for separate tables too. I thought about your suggestion for moving the sold entries. I'll probably use this approach for my project.

Thank you very much.