Hi,
I am currently doing a review of my personal database naming
conventions and found that there are not much (good) examples in the
literature for some special objects. Certainly there are bigger issues
in a database project that that, but I would like to try cleaning that
up.
I am using SQL Server 2005 what requires that constraint names are
unique in the whole database, index names to be unique on table level
at least. I do NOT wish to use any prefixes or suffixes such as PK,
FK, CK, UQ, DF, Trig and so on.
As this however is not a SQL Server specific problem I also appreciate
input from those who do not use this system.
1) Triggers
I am using <action>_<table name> format e. g. AfterInsert_Orders,
InsteadOfInsert_Orders for multi-purposed triggers. If a more
meaningfull name can be found, such as CheckManagers_Employees when a
trigger is used to replace ASSERTION statements, I use this in the
form of <verb><object>_<table name>.
2) Check constraints
This is easy stuff, once triggers are single purposed. I use
<explanation>_<table name> (e. g. PreventSingleNodes_Relations or
ValidateRelationType_Relations)
3) Default constraints
Here it starts to become ugly: Default_<column name>_<table name> and
DummyDefault_<column name>_<table name> for columns which have dummy
values as default such as 'XXXX'. Better ideas here? I did not find
any beautiful solution for this.
4) Unique constraints
More ugly things: Unique_<column names>_<table name>. Better ideas
here?
5) Primary key constraints
PrimaryKey_<table name>. How to name a primary key if not primary key?
6) Foreign key constraints
This is really messy: Using Relation_<referencing table>_<referencing
column>_<referenced table>_<referenced column> stops working when
compound keys are involved and adding all these column names would
result in very long names. An alternative may be Relation_<referencing
table>_<referenced table>_< column names> as I use the same column
names for the same data elements, so the columns which reference each
other have in 99 % of cases the same name - BUT there are exceptions
when an alias name is involved (think at a 'Projects' table with
'EmployeeID' and 'ManagerID' as an alias column name). Any better
ideas are much appreciated.
7) Indexes
As SQL Server does not require unique index names in the whole
database but just in the table, it could be simply <column names>. An
alternative might be Index_<column names>_<table name> to ensure
consistency with the other conventions. For the primary key SQL Server
creates the same name for the clustered index (if there is one
assigned), what is not changeable. Renaming the index for the primary
key also renames the primary key constraint. - As well here, better
suggestions welcome.
Thanks in advance for your replies.
Brgds
Philipp Post
|
