June 24, 2016 at 9:13 am
roger.plowman (6/24/2016)
The ERD is supposed to create the DDL for you. That's what it's *for*... 😀
Umm... no. :unsure:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 24, 2016 at 9:39 am
Have done OLTP databases from scratch, though not for a while. More common these days is to do OLAP from scratch. And while it's almost impossible to get a database with a large number of objects exactly right the first time around, the upfront investment in time to get it as close as possible before doing any application work is well worth it.
June 24, 2016 at 9:53 am
No, but I recently installed SQL Server 2016[/url] so that I could blog and practice. One of the things I'm doing with it is installing a baseball database[/url] so I can practice. I'm hoping my experience with this will be the subject of a future blog article.
I haven't yet imported the data (that's giving me trouble; again, I'm documenting this as I go along), but I did build the tables that were included in the download, and I noticed something immediately: ALL columns are nullable (i.e. there are no primary keys defined), and to my knowledge, there are no indexes or FK relationships defined. Once I get this up and running, my plan is to define PKs, FKs, and indexes for it. Again, I plan to document and blog what I do with it.
I'm hoping that my experience with this will give me a nice background so that, if I need to, I can design and build a database from scratch somewhere down the road.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 24, 2016 at 10:55 am
Eric M Russell (6/24/2016)
roger.plowman (6/24/2016)
The ERD is supposed to create the DDL for you. That's what it's *for*... 😀Umm... no. :unsure:
Ah, clearly you are unfamiliar with the miracle that is ModelRight when combined with Redgate's SQL Compare and Data Compare. You create the model (which is all the documentation, pretty pictures, comments, notes, etc. for the interminable meetings) then let ModelRight create the DDL code--no extra work for the DBA! It's literally 3 mouse clicks to create the entire script! 😎
Not only that, but it has internal consistency checks to make sure the model you create is actually viable when it hits the server. Does reverse engineering too.
A bit pricey, but quickly pays for itself.
June 24, 2016 at 11:15 am
Stefan LG (6/24/2016)
Yes, I have built 3 new databases in the past 2 years that are running in production environments with 3rd party applications and Reporting Services attached.With each one I tried to build onto the best-practices and knowledge gained from previous experiences.
(At least the practices I accept to be ‘best’, since it works!)
However, not everything is known up front, especially regarding integration with other systems and databases (ERP, MES etc.)
Although certain modifications can still be done during the development/testing phase, at some point you have to ‘lock’ the design and just run with it.
Making modifications becomes much more difficult and risky once the database is deployed.
And if I am not mistaken the .NET MVC guys tells me that you can build the model with Entity Framework in Visual Studio and let it create the database for you.
Unfortunately I am too ‘old-school’ for this and believes in ‘database-first’!
Very nice.
June 24, 2016 at 11:52 am
roger.plowman (6/24/2016)
Eric M Russell (6/24/2016)
roger.plowman (6/24/2016)
The ERD is supposed to create the DDL for you. That's what it's *for*... 😀Umm... no. :unsure:
Ah, clearly you are unfamiliar with the miracle that is ModelRight when combined with Redgate's SQL Compare and Data Compare. You create the model (which is all the documentation, pretty pictures, comments, notes, etc. for the interminable meetings) then let ModelRight create the DDL code--no extra work for the DBA! It's literally 3 mouse clicks to create the entire script! 😎
Not only that, but it has internal consistency checks to make sure the model you create is actually viable when it hits the server. Does reverse engineering too.
A bit pricey, but quickly pays for itself.
You're just being facetious. :Whistling:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 24, 2016 at 2:29 pm
I'm only going on 4 years now in my data role and have built a data warehouse (more than one database) and a number of data marts from scratch. I have yet to work with OLTP databases though, only OLAP.
The process of building a data warehouse from scratch was a tough one for me being I had not done it before and I was not a seasoned DBA. I had to do a lot of research, speaking with vets and so on to get those projects to where they are now. Even after the many years of starting the project, there is still lots to improve on with every week or month that I either learn a better way of solving a problem or expand on with new features.
I think by far the biggest hurdle for me in designing a new data warehouse or even a new database is clearly outlining the ERD and all the ETL systems together in a holistic view. It's one thing to model one element logically. It's another thing to model multiple elements logically and tie them into other systems as one larger ecosystem. That was tough for me. Felt like someone dropped a bunch of metal, wood, rope on the ground, then we made a bunch of parts and tossed those back on the ground, then tried to figure out how they all pieced together to make that red Ferrari the boss wanted.
June 24, 2016 at 2:39 pm
xsevensinzx (6/24/2016)
I think by far the biggest hurdle for me in designing a new data warehouse or even a new database is clearly outlining the ERD and all the ETL systems together in a holistic view.
Being able to look at the big picture is far more important than many people realize, yet you still need to be able to see how all the pieces fit together especially when things are in motion. Just like mechanical engineering, blueprints don't show you how things move so you have to be able to envision the moving parts of a system as the data flows through.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 24, 2016 at 4:54 pm
WHUT!!??? Design a database??? Isn't that what ORM tools are for? :sick::-):-D:-P;-):hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2016 at 9:51 am
Eric M Russell (6/24/2016)
roger.plowman (6/24/2016)
Eric M Russell (6/24/2016)
roger.plowman (6/24/2016)
The ERD is supposed to create the DDL for you. That's what it's *for*... 😀Umm... no. :unsure:
Ah, clearly you are unfamiliar with the miracle that is ModelRight when combined with Redgate's SQL Compare and Data Compare. You create the model (which is all the documentation, pretty pictures, comments, notes, etc. for the interminable meetings) then let ModelRight create the DDL code--no extra work for the DBA! It's literally 3 mouse clicks to create the entire script! 😎
Not only that, but it has internal consistency checks to make sure the model you create is actually viable when it hits the server. Does reverse engineering too.
A bit pricey, but quickly pays for itself.
You're just being facetious. :Whistling:
Actually, I'm serious. You have no idea how much time it cuts from development. It's not only a documentation and database design tool, it's a code factory too.
June 27, 2016 at 12:51 am
What's the point of having DBAs if people believe that ORM tools will do it all?
Veeam does backup and point-in-time restores.
It seems to me that one side of Microsoft is eating away at the other side. The dot-Net folks seem convinced now that the database belongs to them, rather than to the SQL Server folks. The dot-Net folks design it, create it and use their performance-devouring Entity-Framework to access it. And all the DBAs do is complain, complain, complain.
We are in big trouble.
June 27, 2016 at 6:39 am
Sean Redmond (6/27/2016)
What's the point of having DBAs if people believe that ORM tools will do it all?Veeam does backup and point-in-time restores.
It seems to me that one side of Microsoft is eating away at the other side. The dot-Net folks seem convinced now that the database belongs to them, rather than to the SQL Server folks. The dot-Net folks design it, create it and use their performance-devouring Entity-Framework to access it. And all the DBAs do is complain, complain, complain.
We are in big trouble.
Maybe. 🙂
Personally, I always thought database design belongs in the developer's hands. Having said that, of course, that makes it the developer's responsibility to do (at least) basic performance optimization and making sure the database architecture uses (at least) sane practices that have some nodding acquaintance with database performance reality.
I always considered the DBA's job to be a second-tier support for database performance issues, as well as the one who tends to the care and feeding of the database (backups, hardware performance tweaks like moving pieces around to different drives, that kind of thing).
As for who "owns" the database (i.e. gets the blame for performance and reliability issues)? That's on the developer.
I've done object oriented programming for years, and interfaced it with databases too. It isn't all that hard, and if an ORM tool can't manage it, well, you're using the wrong ORM tool.
Not that I've ever actually used an ORM...I'm kind of old school that way.
June 27, 2016 at 6:49 am
Personally, I always thought database design belongs in the developer's hands.
It depends on the developer, but in most cases no. When I was a developer, it was okay, because as I would later discover, I could design a database. But I started with Access, where you had to be able to do both. That's not the case anymore. From my own observations I'd have to say that most developers can't grasp the fundamentals. I'm unsure why this is the case as I've done both and seen those who can do both. On the other hand, I'm not as good at the finer administrative skills. Someone else might find that unexplainable as they can easily do both.
So I would advise caution on this point.
June 27, 2016 at 7:05 am
RonKyle (6/27/2016)
Personally, I always thought database design belongs in the developer's hands.
It depends on the developer, but in most cases no. When I was a developer, it was okay, because as I would later discover, I could design a database. But I started with Access, where you had to be able to do both. That's not the case anymore. From my own observations I'd have to say that most developers can't grasp the fundamentals. I'm unsure why this is the case as I've done both and seen those who can do both. On the other hand, I'm not as good at the finer administrative skills. Someone else might find that unexplainable as they can easily do both.
So I would advise caution on this point.
That only shows developers need training. I come from an Access background too, so you're probably right that Access requires learning both skill sets.
The basics of good database design have never been complex, just DRY in a data context. Perhaps Steve has a point and it would be a good series for this site to create.
Might spare DBAs a few ulcers! :hehe:
June 27, 2016 at 7:49 am
Many IT organizations (OK, probably more like most), even some of the largest organizations, just don't "get" database design. Oh, they know all about networking, SAN storage, hardware, and virtualization... but that's not database design; that's infrastructure. They've got 100s of millions of dollars worth of top grade infrastruture stacked on top of poorly designed tables, indexs, and SQL.
And, no, virtualization and automated deployments won't compensate for poor database design. That's not the solution to what's killing your performance.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply