2 ms·
I'm not a big fan of either of those approaches. The cross-implementation schema constraints in SQL DBs are pretty weak. I can only express a small fraction of
by wpietri 14y ago
I'm not a big fan of either of those approaches.
The cross-implementation schema constraints in SQL DBs are pretty weak. I can only express a small fraction of my application logic there unless I want some serious vendor lock-in.
And then regardless, once I'm referencing the same schema from multiple code bases, schema improvements are basically impossible.
If I need to get at the same data from multiple code bases, I think an API is a lot cleaner. And then I'd rather put the constraints in my API code. The tools are so much better, and programming languages are much more flexible.
- PommeDeTerre 14y agoI find your arguments somewhat contradictory. So you want to avoid vendor lock-in, but you see NoSQL databases as being better in some way? They're far worse. If you initially target MongoDB, that's what you're going to be stuck with. You aren't going to be moving to CouchDB easily. It's far easier and much more reliable to use a subset of SQL that's common to the major relational database systems commonly used today. This subset of SQL still ends up being extremely powerful. I'm not sure exactly what you're getting at with the incorrect claim that "schema improvements are basically impossible" when accessing a single database from multiple code bases. There are many trivial ways to offer or retain compatibility. Using views is one common way. You then have much more freedom to change the underlying schema, while still supporting existing queries, for example. Stored procedures, functions and triggers offer other possibilities. An API doesn't magically get rid of compatibility issues, either. You'll need to maintain compatibility within your API, or you'll have to run multiple versions of your API simultaneously, or you'll have to deal with it some other way. All that you've done when using an API is added yet another layer that you'll have to maintain. Duplicating the functionality offered by relational databases within your API's code will only end up taking more of your time. At this point, I think that we've got enough experience as an industry and a craft to know that it's a good practice to use a schema, it's not difficult to change your schema while still maintaining compatibility, and it's better to keep your constraints as close to your data as possible. Relational databases enable this, while NoSQL databases make it much more difficult than it needs to be, if it's even actually possible.
- jeffdavis 14y ago"It's far easier and much more reliable to use a subset of SQL that's common to the major relational database systems commonly used today." I think that is generally a mistake for non-trivial applications. MySQL and SQLite are so far from the standard that it's just not worth trying to find a subset of SQL that works the same on those systems as the others. The similarities between the SQL implementations do make porting easier, however. But I consider it a port rather than using a common subset.
- wpietri 14y agoWhere did I say I was in favor of using a NoSQL database instead? Schema changes without simultaneous multiplatform code changes mean I'll have to maintain a consistent API somewhere. As you say, I could do it in the SQL database with (non-portable) views and stored procedures. Or I could maintain it in code. I prefer that. Not only are the tools better, but if I'm going to have my team spend a lot of time learning something, I'd rather it be the language than particular database quirks.
- jeffdavis 14y ago"I can only express a small fraction of my application logic there unless I want some serious vendor lock-in." I can understand if you're worried about using Oracle or something. But if it's a free software database system, the code is open, and I don't really see that as "lock in". For comparison, if you write an application in ruby, you don't say that you are "locked in" to ruby. "I think an API is a lot cleaner" That can work very well, but it does have some weaknesses. For instance, you may see a check in the API code for out-of-bounds values, and assume your code doesn't need to account for it. But then you hit some out-of-bounds data at runtime, because the data was inserted before the check was added. That's why databases offer many declarative constraints, so that if you see it, you know it's true, and don't have to worry about when the constraint was created. That being said, I think a lot of applications would be better developed using something like a web service to cleanly separate the human interface from the action being taken. I can't think of many ways to do that without a lot of boilerplate code though.
- wpietri 14y agoIt's vendor lock-in either way, although I grant that proprietary lock-in is more problematic than open-source lock-in. Either way, I'm reluctant to place large bets on millions of lines of mystery code with complicated performance characteristics.