I have just been assigned to renovate an old website, and I get to move it from some old archaic system to drupal. The only problem is that it's a real-estate system and a lot of data is stored. Currently all the information is stored in a single table, an id represents the house and then everything else is key/value pairs. There are a possible 243 keys per estate, there are 23840 estates in the system. As you can imagine the system is slow and difficult to query.
I don't think a table with 243 rows would be a very good idea, and probably worse than the current situation. I've done some investigating and here's what I've found out:
Missing data does not indicate a 0 value, data is merged from two, unique sources/formats. Some guessing is involved. I have no control over the source of the data.
There are 4 keys that are common to
all estates, all values look like
something that is commonly searched
for and could be indexed
There are 10 keys that are in the [90-100)% range
8 of these are information like who's selling it, and it's address.
The other two seem to belong with the below range
There are 80 keys that are in the [80-90)% range
This range seems to mostly just list room types and how many the house has (e.g. bedrooms_possible, bathrooms, family_room_3rd, etc)
This range also includes some minor information like school districts, one or two more pieces of data on the address.
The 179 keys that are in the [0-80)% range include all sorts of miscellaneous information about the estate
My best idea was a hybrid approach, create a table that stores important, common information and keep a smaller key/value table.
How would you store this information?
