August 14, 2018 at 12:10 am
Comments posted to this topic are about the item The Master of the Schema
August 14, 2018 at 12:34 am
ORM may be good for things like thrashing out PoCs etc., but after having dealt with several systems that started as "Code First", I've seen enough to convince me that any savings made in the early stages by that approach are in the end nullified and even worse.
😎
Some highlights:
August 14, 2018 at 2:44 am
Eirikur Eiriksson - Tuesday, August 14, 2018 12:34 AMORM may be good for things like thrashing out PoCs etc., but after having dealt with several systems that started as "Code First", I've seen enough to convince me that any savings made in the early stages by that approach are in the end nullified and even worse.
😎Some highlights:
- Retrieving a [Subject-Name] and [Location] required 20+ joins
- 500+ tables had non-trusted foreign key reference to a single column in an EAV table
- Non-serialized GUIDS as Clustered index and PKs on very large and very busy tables
- Simple reports such as Period/Service/User/Cost requiring 10K+ lines of code and still not producing the right results.
- Last but not least, the side effects, multi column indices with tens of included columns, just because someone blindly copied and executed the missing index.....:pinch:
a) Code First and ORMs are two different things - both can be made to work
b) Anyone can get development wrong
On point a) yes, Code First requires tight control to get a workable solution. Guess what, so does writing any complex application. While I accept that bad ORM driven solutions will happen easily I can counter that with many horrible, totally db driven solutions that I have had to work with, with equally egregious characteristics. I've never seen an ORM driven system where proc usage etc is not appropriate but I've worked on many that are very effective and work fast. It's not for everyone but it is viable.
August 14, 2018 at 2:54 am
I find that ORMs are like all tools. Used properly they work as intended. I've only seen them used properly in demonstrations, never in real life.
I always think of them as being like Billy Connolly's description of the device to cut your own hair bought out of the small classified ads in the back of a newspaper and the only time you consider using it is while drunk.
I find it more than a little amusing that there are people selling ORMs for MongoDB.
August 14, 2018 at 6:25 am
As previously mentioned, an ORM is a tool that when used properly does what is expected. Unfortunately, it's the mis-use of an ORM where I have encountered more performance issues than anywhere else. Far too many use Agile as an excuse to quickly generate a prototype system without requirements or planning where code is generated as quickly as possible allowing the ORM handle the database "design". Once the PoC "works" (i.e. with minimal data) then it's slapped into production. Once data volume increases and performance bogs down, it's interesting how quickly the database gets blamed especially if the application generates dynamic SQL. Too often there is a trade-off between speed of implementation and quality of implementation. Speed may seem like a win initially to save money, but it costs much more in the long run with maintenance and re-work.
When the software architect and the data architect work together to plan the project and design not only for implementation but also for scalability and long term usage, great things can happen. Sure, it may take longer to implement and test, but once complete it will save countless hours and money especially in the first year of production. My dad used to tell me, "Do it right the first time." There is wisdom in that because no one ever goes back to do it over.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
August 14, 2018 at 6:55 am
I've been an application developer for over 40 years and I'm a rabid supporter of the "database design is FIRST" philosophy.
But I take a bit of exception to Steve's "learn to script" emphasis, downplaying GUIs. The primary reason I say this is the existence of proper ERD design tools. Things like Rational Rose and ModelRight are first and foremost visual design tools to let you design a database the same way a building is designed--using a CAD tool approach.
They automate documentation of the database the same way architectural blueprints document a building. And then turn around and create the scripts that create the database on disk for you automatically. Then they let you safely modify the design/documentation/development database in a controlled and reproducible manner.
You can even put the ERD under source control. 🙂
With Redgate's SQL Compare (another GUI tool) you can painlessly migrate changes from development to QA to production.
Without manual scripting.
Now, is that to say you don't need to learn scripting? Absolutely not! You need to know T/SQL scripting for all sorts of reasons. But a good ERD design tool emits scripts you can study to see how a properly designed database should be scripted.
ERD tools also presuppose you know how to design a properly normalized database, when to use indexes, the importance of constraints and triggers and foreign keys and all the rest. Every developer needs to know database design, it's a crucial and unavoidable part of development to know the whys of database theory. That's just as important as OOP and in a weird sort of way is a kind of OOP.
Finally, I acknowledge proper database design is a pretty big learning curve if you've never done it. And yes, there are aspects of it that are more art than science. But if you're going to be writing database applications (and face it, very few applications aren't) you need to know proper database design.
Full stop. 😛
Having said that, for God's sake, get some power tools! There's no sense trying to build the Empire State Building of databases using hand-saws and hammers, right?
August 14, 2018 at 8:49 am
roger.plowman - Tuesday, August 14, 2018 6:55 AMHaving said that, for God's sake, get some power tools! There's no sense trying to build the Empire State Building of databases using hand-saws and hammers, right?
I totally agree. There's nothing wrong with using "power tools" when used properly. Use the right tool for the right job! 😛
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
August 14, 2018 at 9:06 am
roger.plowman - Tuesday, August 14, 2018 6:55 AMI've been an application developer for over 40 years and I'm a rabid supporter of the "database design is FIRST" philosophy.But I take a bit of exception to Steve's "learn to script" emphasis, downplaying GUIs. The primary reason I say this is the existence of proper ERD design tools. Things like Rational Rose and ModelRight are first and foremost visual design tools to let you design a database the same way a building is designed--using a CAD tool approach.
They automate documentation of the database the same way architectural blueprints document a building. And then turn around and create the scripts that create the database on disk for you automatically. Then they let you safely modify the design/documentation/development database in a controlled and reproducible manner.
You can even put the ERD under source control. 🙂
With Redgate's SQL Compare (another GUI tool) you can painlessly migrate changes from development to QA to production.
Without manual scripting.
Now, is that to say you don't need to learn scripting? Absolutely not! You need to know T/SQL scripting for all sorts of reasons. But a good ERD design tool emits scripts you can study to see how a properly designed database should be scripted.
ERD tools also presuppose you know how to design a properly normalized database, when to use indexes, the importance of constraints and triggers and foreign keys and all the rest. Every developer needs to know database design, it's a crucial and unavoidable part of development to know the whys of database theory. That's just as important as OOP and in a weird sort of way is a kind of OOP.
Finally, I acknowledge proper database design is a pretty big learning curve if you've never done it. And yes, there are aspects of it that are more art than science. But if you're going to be writing database applications (and face it, very few applications aren't) you need to know proper database design.
Full stop. 😛
Having said that, for God's sake, get some power tools! There's no sense trying to build the Empire State Building of databases using hand-saws and hammers, right?
I'm like you Roger, in that I'm an application developer, but prefer to design the database first. The other thing is more often than not, I've had to design a new application around an existing database. You just can't throw out 10 years worth of data, just because you want to do a code first approach.
But I've got to admit that I haven't heard of any of those ERD tools. Love to give some of them a try.
Kindest Regards, Rod Connect with me on LinkedIn.
August 14, 2018 at 9:14 am
David.Poole - Tuesday, August 14, 2018 2:54 AMI find that ORMs are like all tools. Used properly they work as intended. I've only seen them used properly in demonstrations, never in real life.I always think of them as being like Billy Connolly's description of the device to cut your own hair bought out of the small classified ads in the back of a newspaper and the only time you consider using it is while drunk.
I find it more than a little amusing that there are people selling ORMs for MongoDB.
I agree, but the issue for me is that most people have no idea how they work and build simple systems that don't work or scale well, precisely because the initial flow and defaults that are used by many ORMs that generate code are not well designed
August 14, 2018 at 9:15 am
Aaron N. Cutshall - Tuesday, August 14, 2018 6:25 AMAs previously mentioned, an ORM is a tool that when used properly does what is expected. Unfortunately, it's the mis-use of an ORM where I have encountered more performance issues than anywhere else. Far too many use Agile as an excuse to quickly generate a prototype system without requirements or planning where code is generated as quickly as possible allowing the ORM handle the database "design".
I'd argue that this isn't the mis-use of the ORM, but rather, the poor habit of project managers and developers.
August 14, 2018 at 9:16 am
Rod at work - Tuesday, August 14, 2018 9:06 AMI'm like you Roger, in that I'm an application developer, but prefer to design the database first. The other thing is more often than not, I've had to design a new application around an existing database. You just can't throw out 10 years worth of data, just because you want to do a code first approach.
But I've got to admit that I haven't heard of any of those ERD tools. Love to give some of them a try.
Google "ModelRight" (one word). They have a trial download.
One of the coolest things about ModelRight is it can reverse-engineer a SQL Server database, with ALL the trimmings intact. Constraints, foreign keys, indexes, the lot. Even SPs, although I don't use it for that, preferring to keep the SPs as separate files for granular source control. Gotta say it gives you a running leap to documenting existing databases that don't have any.
Can't recommend ERD tools highly enough! Good stuff.
August 14, 2018 at 9:20 am
roger.plowman - Tuesday, August 14, 2018 6:55 AMI've been an application developer for over 40 years and I'm a rabid supporter of the "database design is FIRST" philosophy.But I take a bit of exception to Steve's "learn to script" emphasis, downplaying GUIs. The primary reason I say this is the existence of proper ERD design tools. Things like Rational Rose and ModelRight are first and foremost visual design tools to let you design a database the same way a building is designed--using a CAD tool approach.
...
Having said that, for God's sake, get some power tools! There's no sense trying to build the Empire State Building of databases using hand-saws and hammers, right?
I'd still argue that scripting really matters. You need to process to be repeatable and reliable. Using a GUI to generate the code, fine, though you'll want to ensure you review the code, at least initially. There may be defaults or choices the scripting engine chooses that you don't want in your final script, especially as the platform can evolve quicker than the tools. You also want to be able to chain these scripts to run quickly, not based on a user pressing the right button in a GUI.
Whether RationalRose or SQL Compare, I'd argue that I want to drive these tools from a CLI that allows the tool to work, but ensures I get a repeatable method of deploying the code. I always want a script, if for no other reason than logging.
I agree that tools are useful and almost necessary once you work at scale or at any pace. They truly to help you visualize your design and work well to ensure you don't forget something. Use tools, Redgate if you find them helpful, or someone elses', but use tools.
Just be sure you can script and you know how to reliably deploy the code they produce.
August 14, 2018 at 9:23 am
Steve Jones - SSC Editor - Tuesday, August 14, 2018 9:15 AMI'd argue that this isn't the mis-use of the ORM, but rather, the poor habit of project managers and developers.
Quite true, unfortunately.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
August 14, 2018 at 11:31 am
Aaron N. Cutshall - Tuesday, August 14, 2018 9:23 AMSteve Jones - SSC Editor - Tuesday, August 14, 2018 9:15 AMI'd argue that this isn't the mis-use of the ORM, but rather, the poor habit of project managers and developers.Quite true, unfortunately.
I agree too. The problems have nothing to do with the tools, it is the lack of methodology. Until someone with some system thinking has laid down some usable database objects, you don't even know what you are building.
August 14, 2018 at 11:41 am
GeorgeCopeland - Tuesday, August 14, 2018 11:31 AMI agree too. The problems have nothing to do with the tools, it is the lack of methodology. Until someone with some system thinking has laid down some usable database objects, you don't even know what you are building.
Even now, I see "Agile" projects where everything is started at once in a shotgun approach. This has developers working without plans or requirements just to get something done. Einstein said, "Never confuse activity with productivity." It's like having builders work on the roof of a house before the walls are up all the while the architect is still making the plans. It's summarized into a single word: chaos.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply