Today I have to restart a projet and due to a specificity of the business (real estate), I will have to store a table for house/apartment/hangar/wahetever. But a single row can contain more than 1000 criteria, and thus, columns (does it have a pool? a fireplace? an helipad, an underground bunker? etc...

But as you can guess, most of these rows will be equal to null (who has a helipad or a bunker at home? very few people...)

Therefore, I was wondering what would be your idea to store it the most efficiently possible? with wich DB? Space on disk, speed, memory usage, etc.... but also to make it as easy as possible for coder to get the datas back (so splitting into multiple tables, for instance, can be an option, but fetching data can be quite annoying with such an architecture)

Also, I would prefer to keep a relational database (mySQL, postgres...) but I'm open to suggestions.

Thank you for your advices!


But a single row can contain more than 1000 criteria

No, you are predicating your data design on a flawed relational model. Putting the cart before the horse. The tail is wagging the dog.

I think you mean that a single entity can have 1000 attributes. In such a scenario, and particularly when most are null, the best solution is usually an Entity-Attribute-Value. There are presumably some attributes which will always be populated, e.g.

address ....

Then store just the attributes which are relevant in a table like this....

CREATE TABLE house_attribute (
attribute VARCHAR(30),
description VARCHAR(128)
PRIMARY KEY (house_id, attribute)

The issue you will come across sooner or later is that someone will want a house with a "cellar" when you only have houses with "basements". Partly this is a user interface issue, but it also looks like a case for using an ENUM data type. But ENUM data types can be tricky to manage particulry when you have a LOT of them and the number of entries changes after creation. Hence you really should provision a list of possible values for house_attributes.attribute as a sperate table and set up a foreign key constraint on house_attribute.attribute.

Querying the data is a little more complex than with lots of columns - but does provide for some flexibility in the case there is not an exact match for a set of attributes:

SELECT house.id, house.address, GROUP_CONCAT(house_attribute.attribute)
FROM house
INNER JOIN house_attributes
ON house.id=house_attribute.house_id
WHERE house_attribute.attribute IN ('helipad', 'bunker', 'swimming pool'....)
GROUP BY house.id, house.address

It all depends on your exact use case, and the data you are going to store. Will you have to do a lot of inserts for new data, or will you be updating data all the time? Or will you be doing searches mostly, and the update/insert is neglegible?

As a rough guess from the info you provided I would create one table with the base propetries for each object (unique ID, address, geolocation, attributes that all, or most of your objects share), then one table with extended attributes as (objectid, attribute name or id, attribute value) maybe using two value fields StringValue and NumberValue, for easier searching of numeric ranges (e.g. number of bedrooms: 3-7). Combining these in queries is not too complicated and will scale reasonably well, provided the right indexes are set. If things get too big, you could create one table per attribute, but that would indeed make queries more complicated.

As for the dbms to use - all relational databases will be fine for such a thing. Some may have limits with too many colums, there used to be a limit at ~200 for some DB I was using some time ago, but not sure if this still holds.

Memory, disk, etc. are impossible to answer not knowing the approximate number of entries and the exact data model.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy