SQLServerCentral / Editorials / SQLServerCentral.com / Three Rules for Database Development / Latest Posts

RE: Three Rules for Database Development

Kristian Ask — Tue, 02 Oct 2012 01:07:52 GMT

[quote][b]Evil Kraig F (9/28/2012)[/b][hr]Fair enough, I know I'm biased due to how it's been used in my experience instead of best practices.[/quote]I think he means that whatever works for you is your best practice.

RE: Three Rules for Database Development

djackson 22568 — Mon, 01 Oct 2012 08:08:44 GMT

[quote][b]Jeff Moden (9/27/2012)[/b][hr]Heh... whatever happened to the idea that you can write bug-free database code? Am I the only one that still thinks that should be the rule rather than the exception?[/quote]Nope.While I don't write as much pure code as I once did, I still rely on good habits developed during that time. I frequently have bugs when I do something new. So I have learned to reuse code as often as possible. I have some processes that receive incoming files and send them downstream to a medical record app. From the start I knew that the finger would be pointed at me when things were missing. So I log everything I touch, and can prove when it wasn't sent. When there is a failure in my process, it is not only logged, but it pages and emails me to let me know. Pretty simple stuff.The first process I wrote is not nearly as good as the last one. The last one is simply a copy of the previous one, with some registry entries changed to point to the correct key, and some small changes unique to that process. 90% of it is reusable code, so the number of errrors approached zero, in fact so far I know of no issues at all.I strive for perfection in accuracy, I design for performance, and I produce code extremely quickly. All because of reuse. There are other ways to ensure bug free code, and while I do not claim to succeed at that every time I code, I do find bugs are usually due to unreasonable schedules and not my methodology.

RE: Three Rules for Database Development

Evil Kraig F — Fri, 28 Sep 2012 13:44:38 GMT

[quote][b]L' Eomot Inversé (9/28/2012)[/b][hr]I don't see the difference in principle between using a spreadsheet, using good source control, configuration management, and version control software, or using an old fashioned T-card rack.[/quote]I'm not against using TFS (or others) for lockout and version histories. The problem is it leads to other assumptions about how things should be written.[quote]So I strongly disagree with the idea that software shouldn't be used to help with source control etcetera.[/quote]Fair enough, I know I'm biased due to how it's been used in my experience instead of best practices.[quote]It's no good saying continuous integration and databases don't meld well, any more than it is good to say that database development always requires continuous integratiopn; the only sane viewpoint is that continuous integration should be used in environments where it is appropriate and aovoided in environments where it isn't appropriate, and claining that everything involving a database falls into one of those heads and not into teh other is just plain stupid.[/quote]If you're talking procs and functions, I agree that continuous integration can work, but most of those changes require schema modifications. The amount of code that goes into supporting schema level continuous integration gets to be inane though. Think of having to hit all the system tables to confirm that the columns for an index are existing for the expected version and in the right order. I'm doing that now because some of our projects enforce CI in both the app and database tiers, and it's a nightmare.Add to that data adjustments are usually one-shot deals but are required as you go between versions. How do you know which data to pull back out if it was based on a previous value? How can you rollback to previous data without either table copies or massive insert lists which might not support the rest of the data that's now also in play in other areas of the hierarchy? You're going to end up going to backup at that point.CI works very well for front end coding because most of their structure is not persistant, it's only important what's there at runtime. I'm sorry you feel my opinion on continuous integration in a database is "stupid", but I'm afraid I stand by that. The only place CI really works from my experience in a database is at the function and proc level, and then only if they don't require underlying schema/data changes, which usually means you're in the warehouse/reporting environment.

RE: Three Rules for Database Development

L' Eomot Inversé — Fri, 28 Sep 2012 13:05:29 GMT

[quote][b]Evil Kraig F (9/21/2012)[/b][hr]I'm also not a particular fan of TFS, SourceSafe, or the rest in the database world. I'm VERY frickin' biased against this, particularly because of the idea of Continuous Integration and Databases do not meld well. Oh, sure, store yourself a version control so you can see what was and what is, no problem. It's also rather handy for lockouts so multiple developers don't step on the same object simultaneously. We used to use Excel spreadsheets for that.[/quote]I don't see the difference in principle between using a spreadsheet, using good source control, configuration management, and version control software, or using an old fashioned T-card rack.So I strongly disagree with the idea that software shouldn't be used to help with source control etcetera.Personally I have worked with databases where the requirements change rqapidly, new features are required by customers that require schema changes, and my team was expected to release new features rather frequently as minor upgrades rather than full releases. So in that environment continuous integration and continuous was the norm - and we were pretty successful. I learnt continuous integration/continuous release in a non-database environment, and it was very successful there too; and I've also worked in situations where continuous integration would have been a mistake. It's no good saying continuous integration and databases don't meld well, any more than it is good to say that database development always requires continuous integratiopn; the only sane viewpoint is that continuous integration should be used in environments where it is appropriate and aovoided in environments where it isn't appropriate, and claining that everything involving a database falls into one of those heads and not into teh other is just plain stupid.

RE: Three Rules for Database Development

L' Eomot Inversé — Fri, 28 Sep 2012 12:50:44 GMT

[quote][b]Phil Factor (9/17/2012)[/b][hr]The idea of saying that one should never do shared database development is ridiculous, ... ... ... to try to anathemise or prescribe the perfectly reasonable practice of shared database development as a consequence, is like insisting that everyone should use crutches merely because you've shot yourself in the foot. [/quote]Well said - you have my 100% agreement.

RE: Three Rules for Database Development

L' Eomot Inversé — Fri, 28 Sep 2012 12:49:42 GMT

[quote][b]Steve Jones - SSC Editor (9/17/2012)[/b][hr][quote][b]Jeff Moden (9/16/2012)[/b][hr]I've found that such "committee decisions" can be horribly wrong especially when people who might not know as much about databases outnumber those that do (think lynch mob, gang rape, and Lemming mentality combined). Everyone working the same way has some very nasty ramifications if they're all doing the same wrong thing. ;-)Rule 1 for database development should be, "Find someone that actually knows how to design databases correctly and then follow their rules."[/quote]This isn't about technical design decisions. It's more about process. We agree with naming conventions, we agree that there is a QA process. We agree developers will turn over unit tests or edge cases. We agree that new tables get a review by the group, or by a person.We decide to work in a way that complements, rather than each person deciding how they like to work.[/quote]For me, Jeff's comment is valid for process decisions as well as for technical design decisions. I've seen catastrophic results of group decisions on process and I don't like them. It is far better to let the development, testing, validation, QA, and release processes be determined by someone who has a clue because he/she has been in charge of a major development including everything from determining and finalising the requirements through limited releases to general release and providing maintenance and upgrades than to count the votes of those who believe they know it all despite not having that experience because they learned it in school and think that testing is not the responsibility of developers, that the waterfall process is viable in an environment where requirements are unstable, and suffer from many of the other delusions of the clueless.

RE: Three Rules for Database Development

Matt Miller (#4) — Fri, 28 Sep 2012 11:35:05 GMT

[quote][b]Jeff Moden (9/27/2012)[/b][hr]Heh... whatever happened to the idea that you can write bug-free database code? Am I the only one that still thinks that should be the rule rather than the exception?[/quote]Well - the dev department here has 40 devs who all insist they write 100% bug free code...:) The hard part I think really comes down to "what's bug free". If you have fluid or squishy requirements, and don't document what it SHOULD do, DOES do, and the background that led you to choose for it to do those things, it becomes a turkey shoot when something bad happens.

RE: Three Rules for Database Development

Kristian Ask — Fri, 28 Sep 2012 03:17:52 GMT

RE: Three Rules for Database Development

Kristian Ask — Fri, 28 Sep 2012 03:15:16 GMT

[quote][b]Matt Miller (#4) (9/27/2012)[/b][hr][quote][b]Kristian Ask (9/27/2012)[/b][hr]Not to look down on test like unit-test but even if the tests are well-written they hardly find more than 40% of the bugs anyway. There's a fine line of when tests are just to overly complicated. You'll need extensive user testing to get above 80%.[/quote]I don't know that I can agree with that. We've been struggling with these kinds of tests for some time and in most cases when the tests became "overly complicated" to implement, it usually came down to code written to "swallow the whole cow in one shot". It may come down to making the code more modular. Test the pieces first THEN the whole.If you break the code down to bite-sized sections, the testing can become very very good indeed. You still need to set up some higher-level end-to-end tests, but at least if you know that the chunks do what you expect, then you should be able to hone in on where the issues are with the multi-tiered testing approach.Still - it's up to each org to figure out how much test coverage is adequate, or how costly those bugs might be to catch later.[/quote]Just don't get me wrong, unit-testing is a force of good but the boundaries are narrow regardless of how well they are written. It's a machine we're talking about and not an erratic user person....I don't think the cost will be very high if there is no test for it but there is a regular release cycle. With proper user and system testing it will be found first time the feature is tried anyway. Some people say it's very expensive but I wouldn't know...

RE: Three Rules for Database Development

Jeff Moden — Thu, 27 Sep 2012 22:30:17 GMT

Heh... whatever happened to the idea that you can write bug-free database code? Am I the only one that still thinks that should be the rule rather than the exception?

RE: Three Rules for Database Development

Matt Miller (#4) — Thu, 27 Sep 2012 10:00:41 GMT

[quote][b]Kristian Ask (9/27/2012)[/b][hr]Not to look down on test like unit-test but even if the tests are well-written they hardly find more than 40% of the bugs anyway. There's a fine line of when tests are just to overly complicated. You'll need extensive user testing to get above 80%.[/quote]I don't know that I can agree with that. We've been struggling with these kinds of tests for some time and in most cases when the tests became "overly complicated" to implement, it usually came down to code written to "swallow the whole cow in one shot". It may come down to making the code more modular. Test the pieces first THEN the whole.If you break the code down to bite-sized sections, the testing can become very very good indeed. You still need to set up some higher-level end-to-end tests, but at least if you know that the chunks do what you expect, then you should be able to hone in on where the issues are with the multi-tiered testing approach.Still - it's up to each org to figure out how much test coverage is adequate, or how costly those bugs might be to catch later.

RE: Three Rules for Database Development

Kristian Ask — Thu, 27 Sep 2012 07:14:40 GMT

Not to look down on test like unit-test but even if the tests are well-written they hardly find more than 40% of the bugs anyway. There's a fine line of when tests are just to overly complicated. You'll need extensive user testing to get above 80%.

RE: Three Rules for Database Development

marlon.seton — Thu, 27 Sep 2012 02:23:04 GMT

[quote][b]djackson 22568 (9/17/2012)[/b][hr]Testing sometimes finds bugs. Occasionally. Once in a while. Maybe only one or two...[/quote]If testing doesn't find a bug the chances are the testing hasn't been thorough enough.

RE: Three Rules for Database Development

marlon.seton — Thu, 27 Sep 2012 02:19:33 GMT

[quote][b]Phil Factor (9/17/2012)[/b][hr]The idea of saying that one should never do shared database development is ridiculous, especially nowadays when you can have the best of both worlds and use database source control whilst doing either shared or single-user database-development, or both. The reason for doing your development work on your own server is merely to allow you to work on different versions of the database 'at once', which means that you can re-create a version from source control in your own private server and fix a bug or add a feature, without affecting your colleagues, or damaging other versions. I've always advocated doing both types of database development, depending on the task. The private databases are fine for prototyping, trying things out and doing sandbox development, whilst the shared model gives us extremely rapid integration, since we have schemas that prevent developers from banging heads too badly whilst sharing, source control allow us to roll back from a development mistake, virtualization to allow us to work on as many versions as we can tolerate. I don't understand why there should be objection to the mixed-model of database development. I understand the build and integration problems that come from database application developments that chose to ignore the best-practice of having defined interfaces between application and database, but to try to anathemise or prescribe the perfectly reasonable practice of shared database development as a consequence, is like insisting that everyone should use crutches merely because you've shot yourself in the foot. [/quote]What he said.

RE: Three Rules for Database Development

Kristian Ask — Wed, 26 Sep 2012 02:06:35 GMT

[quote][b]Steve Jones - SSC Editor (9/17/2012)[/b][hr][quote][b]Jeff Moden (9/16/2012)[/b][hr]I've found that such "committee decisions" can be horribly wrong especially when people who might not know as much about databases outnumber those that do (think lynch mob, gang rape, and Lemming mentality combined). Everyone working the same way has some very nasty ramifications if they're all doing the same wrong thing. ;-)Rule 1 for database development should be, "Find someone that actually knows how to design databases correctly and then follow their rules."[/quote]This isn't about technical design decisions. It's more about process. We agree with naming conventions, we agree that there is a QA process. We agree developers will turn over unit tests or edge cases. We agree that new tables get a review by the group, or by a person.We decide to work in a way that complements, rather than each person deciding how they like to work.[/quote]The problem I've seen emerging lately is that the database is no longer part of the equation by using Entity Framework or other similar framework. In those cases it is even harder to optimally design the database. Last issue I found was triple the number of calls where most of them should have been cached on application load.Currently my db dev team of three have a pretty large shared db and that works just fine as long as they do what I [we] decide and as professional that I require. Nothing more, nothing less. The devs are also aware of what is expected of them...

RE: Three Rules for Database Development

mark-786476 — Fri, 21 Sep 2012 17:04:47 GMT

[quote]I may just be old school, but I can't STAND not having an integrated development environment amongst all the developers.[/quote]Kraig - I completely agree. In an ideal scenario, you test and develop locally and integrate to a central server. That server can have real data for performance and real world testing.

RE: Three Rules for Database Development

Evil Kraig F — Fri, 21 Sep 2012 16:45:54 GMT

I may just be old school, but I can't STAND not having an integrated development environment amongst all the developers. I guess the idea is that your database needs a 'trunk', just like everyone else's front end stuff.How do you test for parallelism on your local box? How do you test the real effects? How can you see what your IO waits will really look like and not just make the excuse "Well it's running off my local IDE, the server will be better"? I work against half tera to terabye level systems in some cases, am I supposed to constantly restore to the 'base state' on my local system? Hell no.I'm also not a particular fan of TFS, SourceSafe, or the rest in the database world. I'm VERY frickin' biased against this, particularly because of the idea of Continuous Integration and Databases do not meld well. Oh, sure, store yourself a version control so you can see what was and what is, no problem. It's also rather handy for lockouts so multiple developers don't step on the same object simultaneously. We used to use Excel spreadsheets for that.However, in my opinion, TFS/SS/etc do not belong in the actual construction of the change scripts though. I don't want to 'have to inherit' off the TFS system for databases, particularly schema and view level changes where you're talking possible massive data manipulations that might be impossible to back out.The shared dev environment contains all the currently working and used code from all developers, allowing for their modifications to directly affect yours during the workflow and development cycle. Allowing dependent changes to affect the optimization testing of items downstream.I've never worked with a QA department that had a chance in hell of testing for that level of optimization or integration of multiple components. They're mostly concerned with end results, not how its achieved and they certainly don't have the expertise to figure this stuff out. It's our jobs as developers to do that and without that integrated dev environment we don't have a shot in heck of pulling it off.

RE: Three Rules for Database Development

mark-786476 — Fri, 21 Sep 2012 16:09:57 GMT

There are a variety of reasons not to develop on a shared database. As others have already mentioned, when you are developing locally you can avoid all sorts of collisions between developers. If you are using MS database projects to do your development, you have a complete copy of your schema under source control and can check out objects and isolate your code changes. It also means that your code does not need to compile when you save an instance of it, which is helpful if you need to shelve your current work. In addition you get static code review and much better ability to handle refactoring your database. The key factor is unit testing. To conduct efficient testing, you should be able to scrap the data, autogenerate more, rename and recreate objects. Database projects allow you to incorporate database unit tests within the same solution so that you can have a tightly integrated base of code. Database projects allow for an easy way to version up or version down any schema. Your team can choose the best way to go about this, but I think more importantly, the implementation of schema should be a separate thing from the definition of that schema. It allows for a much more Agile development environment in which are can be constantly at the ready to implement your code changes.

RE: Three Rules for Database Development

Jeff Moden — Mon, 17 Sep 2012 13:36:54 GMT

RE: Three Rules for Database Development

Miles Neale — Mon, 17 Sep 2012 10:49:03 GMT

Matt, you are absolutely correct. With many hands on one database there is chaos. But that is not the case here. I have a luxury or two that most do not have.

RE: Three Rules for Database Development

Matt Miller (#4) — Mon, 17 Sep 2012 10:29:10 GMT

[quote][b]Miles Neale (9/17/2012)[/b][hr]Some wonder why I install SQL Server on my local box and develop against that version first. It is obvious, never develop the initial product in a shared environment is critical. I also like to have complete administrative control of the data server for the early life of the project, that way I can do what I need when I need it, and it is controlled only on my box. SO I guess I go one step further, never develop on a shared data server and only on one that you have complete control over. If possible. The others a fine.M.[/quote]I can understand if it's a single use, small app. That said- with large databases with a team of 20 developing against it, having 20 copies of the DB each being modified "under full control" of 20 separate devs each with their own opinions is chaos. Without knowing early on that the SAME component needs to satisfy two separate enhancements, all you will end up with is a deployment tug of war.Instead - we've embraced CI and solid check-out/check-in processes. Check out before you modify, check in once you're done and if need be, synchronize and/or normalizae your changes with any source control conflicts that may arise. At least you can ensure that the relevant devs are talking to each other (along with the instructions to come find an architect if some major conflict is found).

RE: Three Rules for Database Development

Miles Neale — Mon, 17 Sep 2012 09:46:52 GMT

Some wonder why I install SQL Server on my local box and develop against that version first. It is obvious, never develop the initial product in a shared environment is critical. I also like to have complete administrative control of the data server for the early life of the project, that way I can do what I need when I need it, and it is controlled only on my box. SO I guess I go one step further, never develop on a shared data server and only on one that you have complete control over. If possible. The others a fine.M.

RE: Three Rules for Database Development

Nadrek — Mon, 17 Sep 2012 08:22:07 GMT

My set of three rules is somewhat different - I say 0) Develop on a full size set of data that is as similar to production as possible. In particular, every wart, mole, blemish, and data violation that exist in production data should exist in your development and test environments.2) and 3) are fine.1) is something I personally believe in, but if your production environment is "large", 0) tends to cause 1) to be expensive. I believe in it if and only if each developer has their environment, and they can make at least one backup/snapshots of it, and restore them on their own. That lets them create wonky test cases as often as they like, and operate in a "frozen" environment when debugging certain tricky interaction effects.1) also requires a shared integration development/test environment, of course.

RE: Three Rules for Database Development

djackson 22568 — Mon, 17 Sep 2012 07:21:42 GMT

Both methods have pros and cons, and probably both methods together are best. The last development job I had worked well this way.When a developer is starting to modify or add code, and needs to change table structures, he is going to need to work on a database in order to make those changes. If during that time someone else is working on another bit of code that just happens to rely on the table structure, the second developer might end up chasing his tail trying to figure out why something isn't working, only to find out it was because developer one made a change. Not very efficient. We can't say the code should have been checked in because 1 is still working on it. In this scenario I think working on your own private copy UNTIL you get things stable is best.Whenever system regression testing occus, it should be on a shared copy. You don't want to test on your own copy and miss changes others have made. So whomever is doing that type of work needs to have a version containing all the changes.As with most things, it depends. Teamwork is key. After that, as long as everyone understands the guidelines, you can work through issues, but in the end I don't see how you can reliably test things without using a shared copy (or whatever name you want to give it) that contains all the code and changes that developers are finished with. Um, I mean, that developers THINK they are finished with. Testing sometimes finds bugs. Occasionally. Once in a while. Maybe only one or two...

RE: Three Rules for Database Development

Steve Jones - SSC Editor — Mon, 17 Sep 2012 07:08:07 GMT

[quote][b]Jeff Moden (9/16/2012)[/b][hr]I've found that such "committee decisions" can be horribly wrong especially when people who might not know as much about databases outnumber those that do (think lynch mob, gang rape, and Lemming mentality combined). Everyone working the same way has some very nasty ramifications if they're all doing the same wrong thing. ;-)Rule 1 for database development should be, "Find someone that actually knows how to design databases correctly and then follow their rules."[/quote]This isn't about technical design decisions. It's more about process. We agree with naming conventions, we agree that there is a QA process. We agree developers will turn over unit tests or edge cases. We agree that new tables get a review by the group, or by a person.We decide to work in a way that complements, rather than each person deciding how they like to work.

RE: Three Rules for Database Development

Jeff Moden — Mon, 17 Sep 2012 06:38:58 GMT

[quote][b]Dalkeith (9/17/2012)[/b][hr][quote][b]Jeff Moden (9/16/2012)[/b][hr][quote]"Find someone that actually knows how to design databases correctly and then follow their rules."[/quote]That will be a real world implementation of Steve's rules then...[/quote]Possibly. I've found that's normally not true though. I've found that even if a company does have someone who really knows how to design databases, they are, many times, simply not listened to because of schedule or notions by unqualified people on what level of normalization is actually needed.

RE: Three Rules for Database Development

Phil Factor — Mon, 17 Sep 2012 05:03:21 GMT

The idea of saying that one should never do shared database development is ridiculous, especially nowadays when you can have the best of both worlds and use database source control whilst doing either shared or single-user database-development, or both. The reason for doing your development work on your own server is merely to allow you to work on different versions of the database 'at once', which means that you can re-create a version from source control in your own private server and fix a bug or add a feature, without affecting your colleagues, or damaging other versions. I've always advocated doing both types of database development, depending on the task. The private databases are fine for prototyping, trying things out and doing sandbox development, whilst the shared model gives us extremely rapid integration, since we have schemas that prevent developers from banging heads too badly whilst sharing, source control allow us to roll back from a development mistake, virtualization to allow us to work on as many versions as we can tolerate. I don't understand why there should be objection to the mixed-model of database development. I understand the build and integration problems that come from database application developments that chose to ignore the best-practice of having defined interfaces between application and database, but to try to anathemise or prescribe the perfectly reasonable practice of shared database development as a consequence, is like insisting that everyone should use crutches merely because you've shot yourself in the foot.

RE: Three Rules for Database Development

call.copse — Mon, 17 Sep 2012 03:39:07 GMT

I'm definitely happy with agreeing rules in a group and then following those rules. As long as the rules are what I want to be done and not anyone else's shoddy, half-baked ideas. :-P Fortunately I generally have the loudest and most authoritative sounding voice...We use a shared database model - I can see why it might be a problem, but it's always worked for us.

RE: Three Rules for Database Development

Dalkeith — Mon, 17 Sep 2012 02:22:34 GMT

[quote][b]Jeff Moden (9/16/2012)[/b][hr][quote]"Find someone that actually knows how to design databases correctly and then follow their rules."[/quote]That will be a real world implementation of Steve's rules then...

RE: Three Rules for Database Development

Jeff Moden — Sun, 16 Sep 2012 17:00:46 GMT

[quote]We can define [font="Arial Black"]whatever [/font]standards, rules, frameworks, processes, etc. [font="Arial Black"]that the group wants[/font], but once we decide, we all need to work along in the manner we've prescribed. That's worked well for me, and when everyone follow the rules we've agreed on, things run smoother. [/quote]I've found that such "committee decisions" can be horribly wrong especially when people who might not know as much about databases outnumber those that do (think lynch mob, gang rape, and Lemming mentality combined). Everyone working the same way has some very nasty ramifications if they're all doing the same wrong thing. ;-)Rule 1 for database development should be, "Find someone that actually knows how to design databases correctly and then follow their rules."

Three Rules for Database Development

Steve Jones - SSC Editor — Sun, 16 Sep 2012 03:55:24 GMT

Comments posted to this topic are about the item [B]Three Rules for Database Development[/B]