March 11, 2015 at 7:04 am
I absolutely agree. Within the software arena there are design standards that must be adhered to as a professional. Database design is a complex as any other facet of software and the designer whether they are a DBA, web or application developer. Professionals gather tools for their tool box and have them available. Training in all aspects of database design is on the web and free for the most part.
March 11, 2015 at 7:59 am
I've spent almost all of my career building desktop line of business apps. I'm probably not as rabidly anal about standards as some of the more vociferous DBAs that walk these halls but I always started with the database design. I want to know that I'm storing the right data and I'm storing it in the right way. I'm not convinced that every bit of validation needs to exist in the DB but I at least want to know that the fundamentals (referential integrity, nullability, base data types etc.) are going to get policed from the ground up.
A couple of months ago I decided to teach myself MVC and, since all the tutorials on line seemed to use a code first approach based on entity framework, I thought I'd give it a punt. I am increasingly horrified by the experience. It absolutely lulls you into a false sense of security while planting potential time bombs of atomic proportions. It's not that the options to create good design aren't there if you go looking for them but rather the defaults it picks if you don't bother to go looking.
It'll create primary keys and foreign key relationships for you based purely on the field names you choose - no need to specify them yourself. Now, if I have a field called ID in my Salesman table it's probably not unreasonable for the engine to assume I want that to be the primary key. And if I have a column in my Order table called SalesmanID I guess you infer that that's a foreign key to the Salesman table - it's starting to get a little shadier at this point but actually it's helping out and it has saved me some work. But the real kicker is the fact that the default is sets the relationship to cascade deletes! Really?! Because the most likely behaviour I want when one of Salesmen up and quits is absolutely NOT to wipe out any record of half my revenue stream. Now that really scares me.
March 11, 2015 at 8:36 am
I have no problem with people coding the UI first and working backwards. When they get to write the stored procedure they define it in terms of the data to be returned. At this point the application developer's job is done.
Then (or concurrently) the database developer (possibly the same person) designs and develops the database and at some point implements the aforementioned stored procedure.
The requirement of the output from the stored procedure is valid (but not without questioning - like all else) but that does not mean in any way, shape or form that it should drive the table design.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 11, 2015 at 9:04 am
If you plan on working with SQL Server, it pays to make the investment in learning how to do it properly. If for not other reason than because you should consider if part of your professional responsibility.
Good advice for both the goose and the gander. Developers aren't the only ones who should learn more about database design -- many so-called DBAs I've run across are just as bad.
There's more to being a DBA than just doing backups and restores.
March 11, 2015 at 9:43 am
ddudley-847177 (3/11/2015)
This is why in the old days the conventional wisdom was that the more experienced did more design while the less experienced did the maintenance. It is now reversed. Now, the neophytes create the messes and the experienced persons come in and navigate the treacherous waters they leave behind. This can be a rewarding niche if you are highly skilled(both technically and politically). But be warned. I do not use the term "treacherous waters" lightly.
Database refactoring can be very rewarding, if you're a consultant and there is an understanding when you walk in the door that the system is fundamentally broken, you're the expert who's going to fix it, and your services are valuable. If the client calls in a consultant, then it means they have probably reached the point where they're facing that reality on some level.
However, if you're a resident DBA or database engineer on staff, and your job is to mop up spills or make whatever minimal changes are required to keep legacy platforms running, then it's not so rewarding.
I'm actually beginning to question the relevence of in-house database engineers. It's not that we can't make a real difference, it's just that circumstances beyond our control marginalize the potential of what we are capable of contributing. There are times when I feel it would better to just walk away and then possibly segway back into the picture once the organization has exhausted all other options and reached that place. Meanwhile we can focus our efforts on greener pasture elsewhere.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 11, 2015 at 9:50 am
I'm actually beginning to question the relevence of in-house database engineers. It's not that we can't make a real difference, it's just that circumstances beyond our control marginalize the potential of what we are capable of contributing. There are times when I feel it would better to just walk away ...
There is an old saying (it may be from the New Testament, can't remember for certain) which sums this up: 'the prophet is without honour in his own land'.
This not only applies to stymied DBAs, but to stymied developers as well. There are usually a few of both in larger dysfunctional IT shops, people who could revolutionise the operation, but held back by the negative politics and personalities surrounding them.
March 11, 2015 at 10:21 am
GoofyGuy (3/11/2015)
I'm actually beginning to question the relevence of in-house database engineers. It's not that we can't make a real difference, it's just that circumstances beyond our control marginalize the potential of what we are capable of contributing. There are times when I feel it would better to just walk away ...
There is an old saying (it may be from the New Testament, can't remember for certain) which sums this up: 'the prophet is without honour in his own land'.
This not only applies to stymied DBAs, but to stymied developers as well. There are usually a few of both in larger dysfunctional IT shops, people who could revolutionise the operation, but held back by the negative politics and personalities surrounding them.
Yes, it's from the New Testament, and the following verse sums up the frustration and resolution:
... And because of their unbelief, he couldn't do any miracles among them except to place his hands on a few sick people and heal them. And he was amazed at their unbelief. Then he went from village to village, teaching the people.
I guess the moral here is, to truely accomplish great things, you must go where you're both needed and received.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 11, 2015 at 10:24 am
I guess the moral here is, to truely accomplish great things, you must go where you're both needed and received.
And avoiding crucifixion in the process.
March 11, 2015 at 10:45 am
I thought this newsletter was designed to educate ... not to shame. Rants like this one serve no useful purpose whatsoever. This particular rant smacks of elitism -- of the author looking down his nose and thinking that others' skills don't measure up to his own.
I think the editorial called on those designing and developing databases to educate themselves in the craft.
There may have been a 'developer bad, DBA good' swipe in the last paragraph -- such digs are not uncommon here -- but I found the language vague, so I'd give the author the benefit of the doubt this time.
March 12, 2015 at 6:45 am
One thing I would like to add to this:
the designer is ignorant, or lazy, or maybe doesn't pay attention because it's not part of their job
namely:
"or is saddled with requirements that force them to make bad choices."
A number of times I have been forced to use NVARCHAR columns where I would have used INT, because TBTP decided that we might one day need to integrate with some system that uses strings instead of numbers for identifier fields. Or FLOAT instead SMALLINT for quantity; nobody buys half a shirt, but "they might by half a pound of jelly beans". They think they are being forward thinking, and maybe they are, but in the mean time they are just making more overhead.
Or a previous architect who insisted on GUIDs for identity columns. I won that fight after his design slowed the system to a crawl.
March 12, 2015 at 9:46 am
Mind if I say something a bit in the other direction?
I have designed more databases and worked with developers designing databases then I want to admit to. And over time I have found something out.
If you design for the needs of the business, then you often violate rules that a good data modeler, DBA and Developer all follow.
It took years to understand that databases are not an exact science and that an RDBMS's are not pure or perfect. Data models and backends are put in place to support the needs of the business being automated. Further, three data modelers might design and structure the database five different "correct" ways.
When I think back over the number of times the data model has been denormalized or calculated data stored in the database, I use to cringe. Now, I look at these as valid needs being met in a more efficient way.
I know many would love to have the database perfect for the DBA. And that is well and good if your primary user is the DBA. If the goal is to make the DBAs happy for all means compromise what the user wants and make the DBA happy. (Joke intended!) But if you are trying to meet the diverse business needs of a very complex business, you may find compromises within the data.
Like many of you I did not like this idea at first, but I came to understand that doing it right is not always doing it perfect, it is doing it the way the business needs it while being as efficient as possible.
Would you rather compromise the database and better meet the needs of the user, or would you rather meet the desires of the DBA and compromise the users system? We seek to compromise neither, but if one must compromise, it best be the datamodel and not the users system.
Not all gray hairs are Dinosaurs!
March 12, 2015 at 10:04 am
I came to understand that doing it right is not always doing it perfect, it is doing it the way the business needs it while being as efficient as possible.
(That crashing noise you hear is the collision of theory with practise.)
Well put, Miles Neale, and I agree heartily with what you wrote. Ideologies, whether of the technological or the political kind, don't always hold up in the cold light of day. I've dealt with a few too many DBAs who insist on designs which might have made Dr Codd happy, but not the business users. (One developer I knew who had some serious run-ins with the DBA often claimed the DBA worshipped 'Codd Almighty'.)
March 12, 2015 at 10:28 am
Kudos to the folks who strive and fight for perfection. There are some of us who appreciate you.
March 12, 2015 at 11:56 am
Xavon (3/12/2015)
One thing I would like to add to this:the designer is ignorant, or lazy, or maybe doesn't pay attention because it's not part of their job
namely:
"or is saddled with requirements that force them to make bad choices."
A number of times I have been forced to use NVARCHAR columns where I would have used INT, because TBTP decided that we might one day need to integrate with some system that uses strings instead of numbers for identifier fields. Or FLOAT instead SMALLINT for quantity; nobody buys half a shirt, but "they might by half a pound of jelly beans". They think they are being forward thinking, and maybe they are, but in the mean time they are just making more overhead.
Or a previous architect who insisted on GUIDs for identity columns. I won that fight after his design slowed the system to a crawl.
It is frustrating when business requirements attempt to dictate things like data types. Of course most of us know the right thing to do, but I'll preach to the choir here for the benefit of laymen sitting on the back pew.
In the scenario where an inventory system may need to support multiple product coding schemes, the right thing to do is implement a surrogate key, because codes for two unrelated products will potentially overlap. In the case of quantity, it makes more sense to record units of measure in ounces or using assumed decimal places rather than than selling fractions of things. Values like "66R-02595" or "1/2 Pound" are just what the end users want to see listed on their computer screens and invoices. Of course if we actually model the database otherwise, they'll hold our feet to the fire when the vendor decides to consolidate or rename their codes.
There have been times when I've wrapped a views around tables that served no other purpose than to recast datatypes and rename columns in such a way that it conformed to user whims. Sometimes it's just to accomodate one extra special user. Of course these are the same guys who will come back and ask for columns to be renamed again (and again) at some point anyhow. But if there is a logical separation, then it's no biggie on the DBA side to make that happen.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 12, 2015 at 1:58 pm
Balancing user and technical requirements, therein is the craft. But never forget Liberace's immortal words: without the business, there's no show.
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply