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.