21 Mar, 2014, Lyanic wrote in the 22nd comment:
HeZkeZl said:
I would say that if this is your use case, then you may not really want a Relational Database. Someone mentioned it earlier in passing, but a schema-less NoSQL database like mongodb may fit your needs way better. (or work well alongside a relational database at least). A system like memcachedb may work for you too, doubling as a key/store memory cache and database all in one. NoSQL databases don't do well with complicated queries, but if you have a known key value (like a vnum), they are great for accessing data quickly (and are built so you CAN query on other fields as well, but not as efficient)
Lyanic said:
This is almost precisely the use case I had. I used a templated hashmap structure on objects (items, characters, etc) that lets me add new variables (properties) on-the-fly. Because of this, there are a large number of properties and most of them apply to < 1% of objects. Not only did it not make sense to have separate db columns for each from an efficiency standpoint, it was actually detrimental from a usability standpoint (forcing frequent schema changes took away the dynamical and easy to use nature that was intended). I ended up deciding on storing them as blobs, but that made queries difficult. Stringifying to JSON solves the issue - whenever I get back to working on it, that is.
plamzi said:
If you have an Items table, and you're stuffing all the less important properties your items may or may not have inside a single stringified TEXT field, then you can add and alter properties at will inside that field without having to modify your schema, or any of your read/write logic. The maintenance savings will quickly add up over time.
This is almost precisely the use case I had. I used a templated hashmap structure on objects (items, characters, etc) that lets me add new variables (properties) on-the-fly. Because of this, there are a large number of properties and most of them apply to < 1% of objects. Not only did it not make sense to have separate db columns for each from an efficiency standpoint, it was actually detrimental from a usability standpoint (forcing frequent schema changes took away the dynamical and easy to use nature that was intended). I ended up deciding on storing them as blobs, but that made queries difficult. Stringifying to JSON solves the issue - whenever I get back to working on it, that is.
I would say that if this is your use case, then you may not really want a Relational Database. Someone mentioned it earlier in passing, but a schema-less NoSQL database like mongodb may fit your needs way better. (or work well alongside a relational database at least). A system like memcachedb may work for you too, doubling as a key/store memory cache and database all in one. NoSQL databases don't do well with complicated queries, but if you have a known key value (like a vnum), they are great for accessing data quickly (and are built so you CAN query on other fields as well, but not as efficient)
I didn't say that all the variables were added dynamically via the hashmap. There's still a lot of traditional, predefined structure that works well with relational databases. Perhaps if I'd designed it from the beginning, I would've gone with some hybrid database solution, but like most MUD development, it's a hobbyist project where things get bolted on over time. I don't even have enough time to finish bolting it on, rather less redesign the whole thing and migrate data.
Random Picks
This is almost precisely the use case I had. I used a templated hashmap structure on objects (items, characters, etc) that lets me add new variables (properties) on-the-fly. Because of this, there are a large number of properties and most of them apply to < 1% of objects. Not only did it not make sense to have separate db columns for each from an efficiency standpoint, it was actually detrimental from a usability standpoint (forcing frequent schema changes took away the dynamical and easy to use nature that was intended). I ended up deciding on storing them as blobs, but that made queries difficult. Stringifying to JSON solves the issue - whenever I get back to working on it, that is.
I would say that if this is your use case, then you may not really want a Relational Database. Someone mentioned it earlier in passing, but a schema-less NoSQL database like mongodb may fit your needs way better. (or work well alongside a relational database at least). A system like memcachedb may work for you too, doubling as a key/store memory cache and database all in one. NoSQL databases don't do well with complicated queries, but if you have a known key value (like a vnum), they are great for accessing data quickly (and are built so you CAN query on other fields as well, but not as efficient)