DB Standards Reality Check
-
Looking for some help from the masses to make sure I make a well rounded, and much thought about decision. So I have the following question, and have give three different responses, you can provide addition answers if you wish. Please help me with a reality check, by providing your input regarding the following question.
When discussing database standards who should be task with developing naming standards for database objects, columns, etc.?
A) The Database Administrator
B) The Data Administrator
C) The is no need to have naming standards for object, columns, etc.
D) none of the above (if you select this please explain why you picked this option).
Gregory A. Larsen, MVP
-
I would add and extra answer
B.1 BOTH (Data Administrator AND Database Administrator)
Usually Data Administrator is more Familiar with Company Related Standards and the DBA knows about the Physical and Technology Specifics of a RDBMS the knowlege of both will compplement each other and hopfully you will get what should be adecuate for your Company
Cheers,
* Noel -
Thank you for the input. Your point is a good one. Truly multiple people need to be involved in developing a standard. Clearly it shouldn't be the DBA and/or the DA that develop the standards. It should be done with involvement and input from everyone that might be affected by the standard. Possibly I incorrectly stated my questions. So let me re-phase my statement.
Who should be the project lead in an organization to develop naming standards for database objects, columns, etc.?
A) The Database Administrator
B) The Data Administrator
C) There is no need to have naming standards for object, columns, etc.
D) none of the above (if you select this please explain why you picked this option).
Gregory A. Larsen, MVP
-
My answer would be:
A) The Database Administrator.
The person responsible for defining the names and meaning of business entities is B) DA
In the absurd case the naming standard for database tables could be T001,T002,,,
With the specification that Business Entity(BE) "Customer" is realised in Database Table(DT) "T001"
There is a M:M relationship between BE and DT
This would be a very clear example of the distinction between the responsabilities of the DA and the DBA. ( And require a good metadata catalog 🙂 )
The DBA&DA working together on this is a better idea, but for naming the actual physical database objects the DBA should definitely be responsible.
/************** note ********************/
Also a naming standard is in IMO not the same thing as the actual naming of things.
A standard could be that tables are named: tSYS[EntityName], procedures are named spCRUD_[TableName][_Insert,_Select,_Update,_Delete] or spRAPP[ReportName] etc..
( where SYS and APP are system or application codes ).
For setting up such a standard A) DBA is the only choice.
/****************************************/
/rockmoose
You must unlearn what You have learnt -
rockmoose - Thank you for you reply. This is definitely valuable input.
Gregory A. Larsen, MVP
-
The Database Administrator should put together the standards, then put them forward for discussion/review among those that will have to implement it. After all a standard is no good if no one is willing to follow it.
Take the lead in the matter, do the hard yards, and let everyone review and think about it. Ideally you'll be able to justify your standard with pros/cons for other standards, and through peer review your standard will become supported and followed.
Julian Kuiters
juliankuiters.id.au -
I would expect the DBA to make the decision on naming conventions, I have made the mistake of letting Data Administrator and Analysts set names for various objects and the system was abysmal.
it went from field names like A, B, C.
to thisisatabletoholdcutomerbillinginformation
I know im just re-iterating what was said above, but hopefully it affirms the idea: DBA ALL THE WAY
-- Alex
-
I am not sure what a Data Administrator is. But....
At the company I work for right now, it is the job of the Data Architects to come up with all the data modeling standards, including the naming standards for the objects for tables and columns. The DBAs are responsible the the naming of objects like triggers, stored procedures, indexes, tablespaces, etc. When the data architects create objects that the DBAs are normally responsible for, they use the DBA standards for them. All of this is in the Oracle environment. When it comes to the SQL Server environment, all bets are off. There is essentially no standards for anything. This is primarilly because the DBAs for SQL Server really do not care. So, as one of teh Data Architects, I have tried to keep everything to the standards established in teh Oracle environment.
So, to answer your question more directly, it is both. They need to work together and establish the standard and both be in total agreement. Then they both need to implement it and stick to it.
-
I believe the answer really depends on how you define DBA, DA, and other roles.
At the university hospital where I work, the "DBAs" are responsible for making sure SQL Server is running, that the backups are being made, and that all patches and service packs are applied. They know very, very little about database design or implementation. I would not want them to specify naming standards, nor would they want the responsibility.
Instead, naming standards are being set by our Decision Support Group (the only group really developing SQL Server databases), with me, with the title of "Data Architect", as the lead. We do not have a "Data Administrator".
The important part is not who sets the standards or even what the standards are, but that there *are* standards and everyone abides by them. I believe group consensus is the best way to develop standards.
-
isnt that like having standards for the sake of having standards, if it doesnt matter what the standards are aslong as one abides by them, why have them - they cant mean much if it doesnt matter what they are.
on our databases, the standard applied are so one can identify individual objects or references, we precede all tables with tbl, all user stored procs with sp, system procs with sp_ (which is a platform standard rather than company choice)
we do not prefix column names, but do suffix identity columns with ID, and all foreign keys have the same names as their primary key parent.
etc.
-- Alex
-
For those interested, I have a Word document with table and column anming standards. And an Access MDB with the approved abbreviations. I would eb happy to send it to anyone that wants to take a look at it.
-
I have to agree that there are a lot of variables not spelled out in the question.
The developers (of applications, and/or data models) should work with an applications-oriented DBA to define standards, and then follow the standards when naming particular entities. These are the people that are closest to the objects and have to live with the names.
Systems-oriented DBA types (operations, systems engineers) shouldn't have anything to do with object naming standards. I wouldn't ask my developers to specify memory settings, name data files, or set up a tape recycle schedule.
-
My environment is like Robert Stewart's. At the large bank where I'm a DBA we call DAs Data Analysts and they enforce naming standards for in-house developed databases. They also have a huge list of standard abbreviations to use in table and column names.
This works well for us. Of course with purchased applications / databases the standards don't apply. You get what you get.
-
We use mostly delivered apps. I'm the DBA for Oracle, SQL, and Sybase ASA 8 databases. We also use a lot of Access front-ends to the delivered DB's as well as stand-alone Access. I also am a programmer. Do you actually expect standards?
I shudder at the naming conventions I have run into:
- One delevered app uses 2 alpha and 5 numeric character for naming tables. The columns are alpha.
- One names the tables as for their purpose, but all column names are prefixed (Key fields have inconsistent naming)
- One uses 13 individual databases instead of one database with different table names
- One has some 35+ character table names and the logical file names have no relation to reality
At some point, some poor schmuck on the distant end of the development process is going to have to DML the tables, or DDL because the indexes are lousy. (Ref Retrieving Bulk Data) Building to confuse is not the answer.
And in my reality, I have as yet to meet a DBA that does not have to look at the data. Very few companies, that I know of, have a DBA that just looks at the external of the DB's and never has to muck in the data.
Just throwing my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Thank you for all the comments. This has been extremely helpful, since I have been tasked with developing Database Administration Standards at company.
Now I'm looking for a quick win here, some how. Although I'm afraid, that if I propose object naming standards it will take years to get everyone to agree on a standard naming convention. Definitely not a quite win.
Any idea how I can have a quick win and have a small set of DBA standards?
More background:
Another problem is I am not the DBA for all SQL Server machines in our company, so other DBA's might not even be willing to discuss standards. This will make it extremely hard for me to develop standards for the company, when not all the players are committed to developing standards. I'm suggesting that management make the decision that all DBA's will work on a common standard, agree to it, and following it. If this can be done at least we will have management saying to all DBA's that they will develop a common set of standards.
To make matters even worse, we don't have a policy in place that say "Thou shalt follow standards, or else....". So I'm saying if we are not serious about putting together a policy stating we will have standard, and we will follow them, then why put any standards together at all.
So now that I've aired my concerns, and I really want a quick win, I'm considering a two phased approach to standards. First phase is to develop a set of easy to agree upon standards, like we will take backups, we will store tapes off line, we will not use db_datareader and db_datewriter, etc. Then phase two can get into those more heated discussions about how we name objects.
Gregory A. Larsen, MVP
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply