To start off, at work I work with a legacy system that, in its day, was quite spectacular, but now is ...interesting... to work with. It uses IBM (now Rocket) UniVerse as its backing database. One particular part of it that has caused some issues is its lack of data-integrity checks. By data integrity, I don't mean file corruption, but rather things like orphaned records or invalid keys. The particular version they use doesn't support things like triggers and such and so unless the programmer remembered to update their computed indexes, it becomes "broken" and filled with bad data. Now, the other programs have been built to live with this bad data, but it is most annoying when putting it into another database, such as MySQL (using InnoDB as the engine) which actually has constraints on the data.
I'm experimenting with MongoDB and NodeJS just to see what all the hype is about. I really like Mongoose as well and its schema system. I've been reading a lot about what to store on each record vs in a separate collection. Perhaps its just my own RDBMS bias, but I decided to store each "type" of thing in a separate collection and utilize Mongoose's "populate" functionality to essentially relate the records together. Now, I am sure someone is going to say that that is going against the whole NoSQL thing, but I haven't really read anywhere that says that just because something stores documents instead of records and has no set schema on the db level that it can't be made relational.
In my experiment, I have "Posts" and "Comments". I see four ways to store the relation between these two:
- The full data for each comment is put directly onto the post as a subdocument. There are two main cons that I have seen with this: If I decide to put comments on something else (let say a "Page") as well, I have to essentially repeat myself and it isn't quite as simple to find out how many comments a user has posted if the comments are actually stored in several collections
- Comments are a separate collection and they store their parent's key and a Schema name for mongoose to use when populating (the schema name switching wouldn't be automatic). This isn't too bad, but its biased towards loading comments first and posts later. Finding out the comments on a post isn't hard, but requires a manual query.
- Comments are a separate collection and posts have a list of comment ids that relate to them. This is biased towards loading posts first and finding out what a comment is attached to becomes difficult. However, mongoose would let me load the comments without having to write anything additional.
- Comments are a separate collection and have a parent id. Posts also have a list of comment ids. This combines the above two methods and neutralizes their cons and makes for relatively few "manual" queries, but introduces the possibility of data becoming dirty and out of sync like the legacy system that I described above (e.g. a comment says it belongs to one post and another post (or multiple posts) claims that they own that comment).
I was going down the latter path listed above and I realized that I was starting to enter the realm of this legacy system that had caused so many headaches with its manually updated indexes and possibility for bad data.
Now, I don't expect to do a ton with this little experiment of mine, but its the principal of the thing that has me thinking. What would you all recommend to go with on this? I want to be able to keep query counts low, but I also don't want to have to remember to update all these indexes. There has to be a happy medium somewhere.
Another option, of course, is to just use MySQL with some nice schema constraints, but that isn't the point of this particular exercise for me since I've done that tons of times already.