1

What is the most optimal way to store lots (up to 300) of properties about an object? All properties determinate presence of a factor. Like, "has dog", "has cat", "has garden", "has N".

Initially I was thinking about a table with 300 columns:

create table my_objects (
    has_dog enum('0','1') NOT NULL,
    has_cat enum('0','1') NOT NULL,
    has_garden enum('0','1') NOT NULL
);

Don't bother the relevance of the enum type, it's there only for extra self-explanatory reasons.

But having those has_ x 300 columns makes my guts feel nervous.

Any advice is much appreciated.

EDIT

Since many people started asking correct questions, here's the additional info:

  • The db will mostly exclusively handle SELECT queries. Selects consist of combination of has_{prop} = 0|1 AND/OR has_{prop} = 0|1. Like heavy filtering.
  • There will be Sphinx/Solr layer on top of db.
  • The amount of properties is constant. So if there's one row in table my_objects, it will have those 300 columns filled, either with default values, or defined.
  • my_objcets table will hold no more than 1 mil of objects.
  • 3
    What RDBMS are you using? What type of queries do you need to do against this data? – Martin Smith Jan 18 '15 at 19:38
  • And why use enum? Most DBMS have a boolean or bit datatype. – ypercubeᵀᴹ Jan 18 '15 at 19:50
  • @MartinSmith, MySQL, Postgre. One of those. 99% of queries are SELECT-s with mixture of AND/OR specifiers for those 300 has_cat = 1, has_garden = 0. – Aleksandr Makov Jan 18 '15 at 20:27
  • @ypercube yes, I won't be using enum, like I said, it wanted it there just to make my requirements more clear. – Aleksandr Makov Jan 18 '15 at 20:28
  • @a_horse_with_no_name yes, always the same number of properties. – Aleksandr Makov Jan 19 '15 at 9:11
1

"Just" 300? That might not really be a problem, actually. How many rows do you expect in the table? If not that many, say, less than a million, then doing that might not be a huge problem.

Or if you're using PostgreSQL, as noted, you can use an array of booleans. That, I think, would be preferrable to putting each column individually in the schema.

The "best" way, however, would be a bitfield. Say, an array of a hundred 4-byte integers, with each value being one of the bits in one of those fields. So your application could have a map of tupes, something like:

has["dog"] = (25, 18)

Noting that 'dog' would be in the 26th field (zero based), in the 19th bit. When you want to query if a record has a dog, you'd need to pull the appropriate tuple and construct an SQL statement like...

(fieldNum, bitNum) = has["dog"] # python syntax

SELECt * from tbl WHERE (has[fieldNum] & (1<<bitNum)) > 0

Finally, Postgres also has bit strings, which I've never used, but it looks like they might do the job as well. http://www.postgresql.org/docs/9.4/interactive/datatype-bit.html

  • This is actually very interesting. Thx. I was thinking about serialising combination of properties into some kind of a hash, and store it as well. But bitfield is something worth trying out. – Aleksandr Makov Jan 19 '15 at 9:13
1

The optimal way to handle it is to table-drive the things. If you have 300 of them now, what are the chances that you're going to have 301 tomorrow? Instead of making frequent schema changes, just keep a table of OWNABLE_THINGS and use a many-to-many relationship to track which things your objects own.

Here are both the conceptual view, and the logical view, which uses an intersection table to persist the many-to-many relationship values.

ERD

In this approach you can simply have a record for each thing which an object has. That way, if an object only has 10 of 300 things, you only need 10 records in your intersection table. That's less data to manage.

Alternatively, if you really, really want to have and maintain 300 data points for each object, you can have a fully populated intersection table and add your has_one bit field to this table.

  • But I suppose that will force me to do (up to) 300 joins, whenever my SELECT goes fancy with dozens of has_dog=1 AND has_cat=0 OR has_garden=1? I'm now trying to evaluate the performance impact over the single table with 300 columns. Although it might not be that problematic, cos I'll put Solr/Sphinx on top of db. The frequent change should not be an issue. One more thing worth noting is that whenever object is created it will have those 300 properties. – Aleksandr Makov Jan 19 '15 at 9:02
  • @AleksandrMakov - If one of your major use cases is searching by a list of particular types of things that your object has, then using 300 bit columns will inevitably result in table scans, so your query performance will has tortoise as it were. Depending on how many of your ownable things a typical object has (i.e. if the average number is much less than 300) then you are probably better off using the pure intersection rather than a fully populated intersection with a bit flag. Treat the absence of a link record as "not owned". This will be at least as performant as 300 bit flags. – Joel Brown Jan 19 '15 at 12:36
  • what do you call "pure intersection"? A column intersecting a row? – Aleksandr Makov Jan 19 '15 at 19:34
  • @AleksandrMakov - A "pure intersection" is a table containing only foreign keys that together make a composite primary key for the table. OBJECT_HAS_THING would be a pure intersection if the only columns in that table are my_object_id and ownable_thing_id and if both of these columns together are the primary key of the table. If you added a third column to this table, say has_one (bit) then it would no longer be a pure intersection. – Joel Brown Jan 19 '15 at 22:38
  • Thanks for clarification. Give me couple of days, I'll make up a sandboxed db and report. – Aleksandr Makov Jan 20 '15 at 8:13

Your Answer

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

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