Managing features for a real estate agency [duplicate]

Question

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table.

The data collected by the agency always uses the same form; a 7 page form structured in different categories.

For example, you have categories like USAGE, INSPECTION INFORMATION, KITCHEN APPLIANCES etc., and within those categories you have several options for example renovation_year. Sometimes that option is a single field but other times it's a multiple checkbox or a dropdown or something else. The form is always the same for every piece of Real Estate.

How would I go about saving that information in the database as normalized as possible?

Should I have a table with real_estate_feature_categories and a table real_estate_feature that has a many-to-one relationship?

How would I link a specific real estate property to a field with a value?

Answer 1

Best to avoid entity-attribute-value structures if possible as they complicate the model and are unnecessarily fiddly to work with. It sounds like the fields are going to be the same or substatnailly the same across all the houses, so you're probably just better adding them onto the property record.

The exception being if you really have a genuine 1:M relationship. An example might be if you wanted to record (say) dimensions and room type (living room, bedroom, bathroom etc.) by room for a variable number of rooms.

Answer 2

If you don't need to sort or search features, then using a CLOB might be fine, storing all the features as XML, which you can still query and is easy to format to HTML.

If there are business rules or sorting or searching by feature required, then you should really use a proper column to store each item.