January 4, 2007 at 12:43 pm
Interesting. Im beginning to wonder if dateadded shouldnt fall into the special case of auditing, and auditing in my experience is best done via trigger, omitting it from the base table altogether (or combined in a view if needed).
As far as doing updates that bypass the trigger, I've found it useful to have an exception built into triggers so that they do not fire on a selected 'admin' machine so that I can easily make corrections without winding up tainting the data or worse.
January 4, 2007 at 12:49 pm
I've never tried that approach though i have done the opposite (with procedures, not triggers) that would only run if the calling user was logged in with my SQL account (which is only me, of course).
January 4, 2007 at 2:16 pm
You seem to be taking this very personal. I'm not sure what that's about, but I'm sure it's nothing that a few hours on a couch can't resolve.
Yes, we use coding standards and best practices here. I wrote the coding standards that we use here. But my coding standards do not have to be exactly what YOU think is best nor is it required that it conforms to a standard written by someone else.
You don't have to agree with my coding standards. I certainly don't agree with yours, but I'm not resorting to elementary school tactics like calling people names and trying to bully them into following my rules.
You should be the last person talking about being professional. That kind of behavior has no place in a professional setting.
January 4, 2007 at 2:22 pm
Let's try to stay on topic. Cluttering up discussions attached to content with stuff that doesnt directly pertain just makes it hard on the next person. Standards, identities, all good topics - go start a new thread!
January 4, 2007 at 2:34 pm
I have a couple questions on that. If not triggers, what auditing technique - something along the C2 switch & profiling? And in the case of SOX, it would seem more imperative that if you have a default set for something like DateAdded that it not be overriden by the average developer who might not realize that the default was there - sorry, thats not quite a question - how about this, maybe the default is so important it should be protected by either a trigger or a view?
January 4, 2007 at 4:17 pm
We make a fine product, OneData Registry, that will handle all of your ISO 11179 needs (:=).
Actually, we do! http://www.datafoundations.com
January 5, 2007 at 3:01 am
I enjoy Joe getting involved .. I might not always agree but you cannot ignore his knowledge or skills, sorry Robert !
I had a rant at the developers at one contract who were defining every column except the identity ( sorry Joe ) on their tables as null .. their response was to set defaults of 0 for numeric and '' for character columns .. cool huh ?
As an aside to that I often find that in a dot net dev environment the managers rarely consider sending their developers on the sql server training courses - this tends to degrade database design and lead to problems.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 5, 2007 at 3:37 am
OK, my biggest beef (well, one of them (:=)) is separating the front and back end development. They are two sides of the same coin, and should be done by the same person.
Of course, said person should know how to program both ends
January 5, 2007 at 8:53 am
99% agree with Joe here. I can always tell when a database has been coded by an application developer(s) they are universally terrible. Keep them separate and design the database FIRST. Treat the application as the presentation layer for data that it is, rather than treating the database as a storage mechanism for the application.
I also agree that it's a problem that the app and DB don't share the data dictionary, but as long as applications are built using hierarchical data constructs (OO) it will always be that way. The real problem is that most of the time when the "impedance mismatch" is invoked, the database is assumed to be at fault when it is actually the programming languages that use the demonstrably inferior data constructs.
Sorry Andy, but I can't resist... Here's where I disagree with Joe. Identities are fine for some things. Obviously they don't work so well when you need to ensure gapless sequences etc... And I just laugh (cry) when I see a database that has an identity on every stinking table. But when you need a system generated surrogate key, why not? Nothing else will perform as well or be so convienient (at least in the SQL Server world). I do wish MS would implement sequences though... There is nothing "non-relational" about identities, proprietary and non portable...yes, but since the RM does not proscribe them and does not prescribe how surrogate keys must be generated saying they are "non-relational" is hyperbole.
As for using surrogate keys for code tables, I generally don't like the idea. Sure it allows you to redefine the code without making a bunch of updates, but I don't view that as being a real positive in most cases. As for the negative, it forces you to do a lot of otherwise unnecessary joins, hurting overall performance.
100% agree that statuses are NOT entities per se, they are attributes whose values can and should be constrained by a FK but that doesn't make them entities. Thinking of codes as entities leads one to use MUCK tables.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 5, 2007 at 8:55 am
I guess I hold differently. When you have more than one person working on a single function, then you have communication issues. When I develop sw and do both the front and back ends, there're no mapping issues, for example.
Actually, ideally, to me, the same person gets the specs from the end user(s), writes the documentation/manuals, does the front end and back ends, testing, etc. This way, I optimize for people who understand the business; after all, without the business, there's no show (Liberace).
January 5, 2007 at 9:01 am
Sorry if I wasn't clear. The data model is the heart of any application, whatever data store is used. That should be designed and understood before any development is done.
And if application developers design terrible databases, they should be trained how to design better databases, not fob off the design to someone else. People can learn and do more than one thing, of course (:=).
January 5, 2007 at 9:04 am
The problem is that application development and database design require two totally different skillsets and mindsets. I've never met anyone who could effectively do both. I've met a bunch of people who THINK they can do both, but reality is very different.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 5, 2007 at 10:56 am
In this respect, Joe and I are in absolute agreement.
January 5, 2007 at 11:05 am
The purpose of the data dictionary is to perform the communication. If the application developer doesn't understand it then it's time to ask the database developer for clarification. If nothing else, it serves as a focal point for the application and database developers to work together and make sure everything is laid out properly.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
January 5, 2007 at 1:49 pm
"I've never met anyone who could effectively do both."
Well, I'm going to be immodest here and say that I do both sides of the coin fairly well, and I expect that the developers who work with/for me do as well. I need people who learn the tools needed to do the job, whether they're front end, back end, scripting, OO, whatever. I resent like heck the time and money wasted "mapping" an OO hierarchy to a relational db structure. Makes no sense to me.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply