June 23, 2016 at 9:42 pm
Comments posted to this topic are about the item Have You Designed a Database from Scratch?
June 24, 2016 at 3:23 am
Sadly, I'm so long in the tooth that I have designed entire databases from scratch (usually as a collaboration), but admittedly, not in recent years.
However, I have found by talking to recent graduates that database design is either not taught at all (at degree level), or poorly taught, at our (UK) universities; relational database theory is out of fashion and guys prefer a non-SQL approach if possible, as then they understand it (don't get me wrong, No-Sql is great in some situations).
Worse, has been having my son asking about the best way to complete database design in his degree assignments (NCL, sorry). It was revealed that the person teaching the course didn't seem to know much about implementing E-R diagrams into tables for real-life use. My sons' work was panned by the post-grads marking it, who were only able to compare it to the crib provided: even on moderation, there was no understanding that you might key of tables using surrogate or identity columns, or the pitfalls of keying tables on user-entered columns, and they showed an incomplete grip of 3rd normal form. Eventually I counselled my son, for his exam, to answer the questions in the expected manner and not use the industry best practise we'd discussed.
A bit scary for our future databases.
June 24, 2016 at 4:27 am
kerry_hood (6/24/2016)
A bit scary for our future databases.
Way back when I was in school (still in the days of green bar line printers) subjects like database theory, set theory, and assembler programming were among my courses. Unfortunately, courses like those have set aside in favor of the latest trends in technology. Not that there's anything wrong with learning about new techniques and tools, but the basics seem to be falling by the wayside. What happens as a result is what you just described. While students come out with an excellent foundation in new technologies, they are ill-prepared for the majority of tried and true technologies still being employed. I believe that Hadoop will survive and thrive yet numerous technologies have cropped up over the years that did not survive test of time (remember when SmallTalk was all the rage?). Without a solid foundation in the basics, what will happen to tomorrow's workforce?
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 24, 2016 at 5:38 am
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’!
June 24, 2016 at 5:47 am
I've lost track of the number of database applications that I've built from scratch over the last 30 years. One thing is true though, I never got them completely right the first time around! 😛 While experience has provided me with better starting points each time, there's always something that either the requirements didn't state, situations changed, or things that I simply didn't know about until later. Building your database design with extensibility in mind is critical!
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 24, 2016 at 6:36 am
At this point, I've designed a number of application and data warehouse databases from scratch. I've been the only or senior SQL guy at my last few jobs so I've taught myself (mostly by reading a couple of excellent books and Google searches). It's been both a frustrating and rewarding experience. Hours worrying over the use of VARCHAR vs NVARCHAR, data integrity, indexes, etc. Relief and excitement when the database performs well. It's probably my favorite part of database development.
I've never really though about blogging about my experience as I feel that someone else has done so better. Comparing yourself to Community Intelligence is pretty intimidating.
Also, I've found you're never really done :).
June 24, 2016 at 6:48 am
I've definitely build many databases from scratch. My main project - a .NET client/server application written using WinForms with a SQL Server database - has a total of 10 separate SQL Server databases with a total of ~380 tables, ~75 views, and ~650 stored procedures. We're updating these all the time and as we add new functionality to the application, if it's a completely separate "business area" we'll probably create a separate database for it (from scratch).
June 24, 2016 at 7:24 am
I'm another who has designed and created more databases from scratch than I know. I'm one of those weird people who actually enjoys it. 😉
I believe that no database design is 100% on the first pass. Things change. The rules, entities, etc. all change over time as the system gets used. People extend the system to do more than the original specification, which may or may not have been right. A database is, many times, a near-living thing that grows over time. The key is to follow good design practices and allow for updates when the changes come. The only thing that's certain is that change will come.
June 24, 2016 at 7:34 am
Being a lone wolf developer I've not had the luxury of NOT designing databases from scratch. Been doing it for 16 years now and learned a few things along the way.
Personally, I can't see how a developer can write a decently performing application without a complete knowledge of the database. It simply floors me that relational database design isn't a skill application developers think they need.
I'm not talking about the subtle nuances you only learn from deep immersion either. I mean basic skills like third normal form, indexing, primary keys and so on. These skills are pretty much required to properly create a database that has any chance of performing at better than a snail on a glacier speed.
Sure, every platform has its black arts, but not knowing a database needs indexes at all? Really?
Everyone reading this has their favorite horror stories so I needn't elaborate. Having said that, the most heart-felt advice I can give any developer is understand basic database design theory.
It's not even that hard once you accept the SPOT logic (Single Point Of Truth).
June 24, 2016 at 7:46 am
In many organizations (OK probably most) the application developers create the database; both the logical and physical model. The DBA discovers that this new database will soon exist in production only after getting a meeting invite for the deployment planning. The DBA looks at the design, makes some hasty reccomendations about non-trivial issues like data types, normalization, and keys. Maybe the developers are interested in these reccomendations, or maybe not. In either event they don't appreciate the potential road block of last minute redesign. After all, at this point coding is comple, it's past through "QA", and it's been signed off on by the business, so they're naturally reluctant to change anything at this point. "Thanks but we'll get around to implemening your suggestions in Phase II.", they say. What's important to them now is to get it into production.
On the flip side, another problem is when the DBA attempts to create the database in a silo based on a few scant conversasions or white board diagrams, but he or she doesn't truely understand the real world business problem or end user requirements. While the DBA struggles to wrap his or her head around what the users really need, the developers are chomping at the bit and complaining that they can't start coding until the database is in place.
So, instead, simply let the business analysts and developers create the logical model (the high level ERD or Visio diagrams), and then let the DBA create the physical model (the actual DDL for the database objects). Do that, and you may start loving IT again.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 24, 2016 at 8:00 am
roger.plowman (6/24/2016)
Being a lone wolf developer I've not had the luxury of NOT designing databases from scratch. Been doing it for 16 years now and learned a few things along the way.Personally, I can't see how a developer can write a decently performing application without a complete knowledge of the database. It simply floors me that relational database design isn't a skill application developers think they need.
Same boat here. I not only design databases from scratch but the applications to go along with them (and like Ed, I love doing it). Some of the approaches I took I look at now and think "What was I thinking??" or maybe I've learned a better solution or pattern in the interim so I go back and fix it. Fortunately I've taken care to use things like views, stored procedures and even Service Broker to abstract/decouple the design elements so altering them is much easier.
To be sure, the tools and frameworks Microsoft releases for their stack "dumb it down" to the point where databases appear little more than backing stores for application objects, so it is little wonder developers don't take the time to really understand them.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
June 24, 2016 at 8:28 am
I'm fortunate to work in an environment where there are always new database projects emerging. I've created many, many databases, data warehouses, and front-end apps to go with them. I have wrestled with all the usual issues of bit v date, varchar v. nvarchar, surrogate v. natural keys, normalization v. denormalization, plural table names v. singular table names, camelCase v . TitleCase v. Under_Scores etc., etc, etc. Here are some of my lessons learned so far:
--Model your entities correctly and let the reporting chips fall where they may.
--Remember that the project is never finished. This is a variant on "don't let the perfect be the enemy of the good".
--Use a table specific surrogate primary key name and then reuse it as the foreign key name whenever needed.
--Every table needs a Date_Created field.
--Data integrity rocks.
--Send as little data across the wire as possible in as few calls as possible.
--The Interview is the most important (and hardest) step in the database creation process. Often, the developer will have to know more about the client's business model than the client him|herself knows.
--CLR can make your life easier and harder at the same time.
--Tools not Rules. It's about getting the job done, not about dogma.
For what it's worth...
The three biggest mistakes in life...thinking that power = freedom, sex = love, and data = information.
June 24, 2016 at 8:47 am
Rod at work (6/24/2016)
Wait, what's this about a Guidebook app?
Maybe we can startup a SCRUM team using folks from here at SQLServerCentral.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 24, 2016 at 8:51 am
The ERD is supposed to create the DDL for you. That's what it's *for*... 😀
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply