Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

marked as duplicate by jcolebrand Sep 12 '12 at 6:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

up vote 3 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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