Mysql Subtype and Supertype - database modeling

Question

I'm about to develop a personal project for learning purpose. A real-estate web site, where advertisers (registered users) can publish ads for their properties (apartments, lands, houses, ...) either for rent (for a defined period or undefined period) or for sale. Also where clients (visitors) can search for specific type of properties for rent or purchase in a specific location.

Business rules:

• User is a registered one.
• User has one Role and only one Role (user, admin, …).
• Role belongs to many Users (default Role for each User is user).
• User has one Location and only one Location (location refer to address).
• Location belongs to many Users.
• User has many Properties (either for rent OR sell).
• Property belongs to one User.
• Property belongs to one Location (location refers to address).
• File belongs to Property.
• Property has many Files (basically images).

I did so many Google searching, seen some database modeling in databaseanswers.org but I’m stuck on property’s type/features, I can't really figure it out, I don’t know the best solution for this!! Because each type of property has different features, for example apartment has rooms, bathrooms... but lands no, for lands we could say they are in-town or out-town…

I want to design this database to be more maintainable and scalable. I don’t want the database design structure to cause more complex queries/code while developing.

I would appreciate if you could give me some advice about my “little” issue.

Do I have to separate the Type of each Property to their own Entity (table) for example Apartments table, Lands table, Houses table … That way I can attach them with their own Features.

Or do I have to merge them in one table "Properties" with all the Features, and have another table "Type" to link each Property to its Type (like in Posts and Categories).

Or do I have to create four table "Properties" "Type" "Features" and a pivot table "feature_property".

• Property belongs to one Type (Apartment, Land, House, …).
• Type has Many Properties.
• Feature has many properties.
• A property has many Features.

Answer 1

There's another alternative that's closer to the logical roots of relational modeling - creating a table per feature, with a column for the property id and attributes to measure or describe the feature. A record in such a table means the feature applies, no record means it doesn't apply.

As for which approach is best, that depends. The correct way to do it is to build a logical model first in terms of sets, dependencies and constraints, then construct tables to implement the logical model. I suggest you take a look at object-role modeling which is a sound discipline for conceptual/logical modeling.

That said, we can compare different physical approaches:

First, combining a bunch of nullable attributes into a single table is simple enough, but nulls introduce complexity that you'll have to deal with in your code and queries, and watch out for hidden dependencies among the attributes. This works best for simple orthogonal attributes, when you've got attributes that depend on each other it may be better to move them to a subtype/feature table.

Subtype tables work well when you need to enforce constraints on a subset of things. For example, if every apartment must be associated with a management organization which don't apply to lands and houses, this can be enforced via subtyping.

What you call a pivot table works well to tag or associate things but not so well to measure things. I'd consider this for simple yes/no situations and if tags can be added or removed by the user, probably not if I had a fixed set of features, and almost certainly not if the features had parameters.

The per-feature table I described above allows you to combine features as you want, and FK constraints can be used to make some features depend on others. It's powerful but can result in more tables, which increases the cognitive load on developers. This is really a variation of the subtyping approach.

I use all these approaches in my projects. Remember that even experienced database developers can easily miss anomalies when building tables without a logical model, so it's important to know the normal forms and keep dependencies in mind. Better yet, write them out and check them, it costs a lot less than having to fix inconsistent data.