9 ms·

I may be in the minority, but I rather like the rigidity of a fixed schema. Yes, it's a bit more "painful" for initial setup (you have to actually create the ta

by sehrope 13y ago

I may be in the minority, but I rather like the rigidity of a fixed schema. Yes, it's a bit more "painful" for initial setup (you have to actually create the tables) and yes, it's a bit more work during migrations (you have to actually add the columns), but I don't see either of those as getting in the way enough to give it up. It's just too useful. Data structures are meant to last much longer than application code. Anyone that has worked on a long running system can attest to that; well defined data structures and table layouts will outlive any application code. When I'm designing a system, I think I spend orders of magnitude more time thinking about data structures then actually implementing the CREATE/ALTER TABLE code for them. Planned properly, you can even do the ALTERs/CREATEs necessary to add columns in advance of any actual app usage (ie. "two stage" app deployment). There is a place for "flex fields" or storing generic "documents" but legit use cases are pretty rare. When they are necessary, a single JSON column is usually enough. The example I generally use is an audit trail: The who (FK to user), what (event enum), and when (timestamp) are all strongly typed but you may want a JSON field for event specific data. Oh and if anybody has every tried to do a data migration with a schema-less database ... well have fun with that. Either you bite the bullet and convert everything or you end up with a lot if/then/else logic littered through your app that will bite you down the road.

georgemcbay 13y ago
I agree. Early on in my career I had a general distaste for relational databases but after finding my way back to using them I now realize that most of the issues I had were really just limitations of the software (and to some degree the hardware) at that time that caused large amounts of design-time analysis paralysis. eg. WILL THIS VARCHAR COLUMN EVER BE BIGGER THAN N? How should I size it? I don't want to size it too small and then have to rewrite the column... But bytes are precious... Oh, dear! Now I just make it a TEXT in Postgres and don't worry about it. Obviously you might have good reasons to limit the size of a text column for other reasons (security, interoperability... YMMV depending upon lots of factors), but it is nice to not have to think about it if you otherwise have no reason to think about it. The actual relational bits of the relational database never bothered me and in fact I quite liked the abstraction they provided, but I really hated how rigid the systems were at the column level, which is now pretty much solved.
tjr 13y ago
After years of using relational databases, I've been learning MongoDB a bit recently, just out of curiosity. I can imagine the flexible structure being useful for quick prototyping, or for knowledge representation research, or other particular instances where you really don't know exactly what you're going to need to store, and you don't need much in the way of the "relational" properties of a relational database. But for most real-world work... I find it hard to imagine preferring a document database.
prodigal_erik 13y ago
I'd put it more strongly. If your apps are anything more than dumb opaque storage, if they ever process the data they write, they have a schema. There are certain fields and values your code is relying on to work as intended. "Schemaless" merely means your schema is not written down anywhere, and you might not have any tests that could detect whether any version of your apps ever wrote data that violates it. I say "apps", plural, because in my experience if the data store has been there for more than about a year, anyone who thinks there's just one piece of code that uses it is in for a very unpleasant surprise. On data migration, I don't think the if/then/else version is even feasible. If there have been n versions of your apps, there are 2^n possible states any particular record could be in depending on which versions of your apps did and did not update it. I've seen Notes documents after a few years of this that are in such weird states that not even the dev team could say just what the hell happened to that doc or what correct (well, least bad) behavior of the apps would be, much less what the current versions of the apps would probably do. You can kind of get partway there with apps that have existed and been actively maintained for as long as any of your data has been there, but trying to write anything like new analytics over old non-migrated data is hopeless.
jakejake 13y ago
To me it seems like using NoSQL simply to avoid the hassle of creating a schema is not really a good reason to choose it over a relational DB. Each solution brings it's own pros and cons. It's helpful to understand what kind of tradeoffs you're making when you choose one over the other.
politician 13y ago
> you have to actually add the columns That's easy. Renaming, splitting, changing data types, or altering character encodings... on in-use production data? That's harder, and you may need to take downtime. On the other hand, document-oriented databases allow in-stream data model changes. More code, of course, but with care you can spread the cost of the upgrade over time. I'm not a huge advocate for document-oriented databases, but there are trade-offs.

by sehrope 13y ago

georgemcbay 13y ago

I agree. Early on in my career I had a general distaste for relational databases but after finding my way back to using them I now realize that most of the issues I had were really just limitations of the software (and to some degree the hardware) at that time that caused large amounts of design-time analysis paralysis. eg. WILL THIS VARCHAR COLUMN EVER BE BIGGER THAN N? How should I size it? I don't want to size it too small and then have to rewrite the column... But bytes are precious... Oh, dear! Now I just make it a TEXT in Postgres and don't worry about it. Obviously you might have good reasons to limit the size of a text column for other reasons (security, interoperability... YMMV depending upon lots of factors), but it is nice to not have to think about it if you otherwise have no reason to think about it. The actual relational bits of the relational database never bothered me and in fact I quite liked the abstraction they provided, but I really hated how rigid the systems were at the column level, which is now pretty much solved.

tjr 13y ago

After years of using relational databases, I've been learning MongoDB a bit recently, just out of curiosity. I can imagine the flexible structure being useful for quick prototyping, or for knowledge representation research, or other particular instances where you really don't know exactly what you're going to need to store, and you don't need much in the way of the "relational" properties of a relational database. But for most real-world work... I find it hard to imagine preferring a document database.

prodigal_erik 13y ago

I'd put it more strongly. If your apps are anything more than dumb opaque storage, if they ever process the data they write, they have a schema. There are certain fields and values your code is relying on to work as intended. "Schemaless" merely means your schema is not written down anywhere, and you might not have any tests that could detect whether any version of your apps ever wrote data that violates it. I say "apps", plural, because in my experience if the data store has been there for more than about a year, anyone who thinks there's just one piece of code that uses it is in for a very unpleasant surprise. On data migration, I don't think the if/then/else version is even feasible. If there have been n versions of your apps, there are 2^n possible states any particular record could be in depending on which versions of your apps did and did not update it. I've seen Notes documents after a few years of this that are in such weird states that not even the dev team could say just what the hell happened to that doc or what correct (well, least bad) behavior of the apps would be, much less what the current versions of the apps would probably do. You can kind of get partway there with apps that have existed and been actively maintained for as long as any of your data has been there, but trying to write anything like new analytics over old non-migrated data is hopeless.

jakejake 13y ago

To me it seems like using NoSQL simply to avoid the hassle of creating a schema is not really a good reason to choose it over a relational DB. Each solution brings it's own pros and cons. It's helpful to understand what kind of tradeoffs you're making when you choose one over the other.

politician 13y ago

> you have to actually add the columns That's easy. Renaming, splitting, changing data types, or altering character encodings... on in-use production data? That's harder, and you may need to take downtime. On the other hand, document-oriented databases allow in-stream data model changes. More code, of course, but with care you can spread the cost of the upgrade over time. I'm not a huge advocate for document-oriented databases, but there are trade-offs.