RE: naming conventions (constraints and indexes)
Date: Thu, 10 Nov 2011 14:28:26 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4454B0F10_at_LITIGMBCRP02.Corp.Acxiom.net>
We've used various unique suffixes to identify the different object types, similar to what you listed, but what I've found is problems with the base name. What happens when you have a long table name, such as ACCOUNT_TRANSACTION_HISTORY_TB. Ignore trying to come up with a better table name for now as the example is contrived. I was on a project in a previous company where they decided than any dependent objects would use a 4-character abbreviation off the table name, using the first initial of each "word" in the table name. If the table has only 1 word then use the first 4 characters of that word. If it has 2 words then use 2 characters of each word, etc.
So in this example all objects based on ACCOUNT_TRANSACTION_HISTORY_TB would use ACTH to identify the object it's based on. Indexes would be named like "ACTH_<col abbrev>_PK", having a similar convention for columns involved. A PK on ACCOUNT_ID and ACCOUNT_DT would be named "ACTH_AI_AD_PK". We had rules to deal with conflicts, pretty much any issue, but I won't bore you all with those details.
Incredibly cryptic? Yup, but we never had issues with running out of room for object names, followed the same logic for everything and were able to nearly include all important info on object names to help make those names have all key info. Eventually you get use to it and automatically decrypt without issue.
Take it for just another example of what others have done. This example was from a HIGHLY normalized OLTP db. We also had (and maintained) conceptual, logical, and physical data models, which I haven't seen since on any project.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: Wednesday, November 09, 2011 1:18 PM
To: oracle-l_at_freelists.org
Subject: naming conventions (constraints and indexes)
Its an art and a science isn't it. I like suffixes for some things, and prefixes for others. I'm limiting my question to constraints and associated indexes and only the suffixes. I use suffixes for indexes. (Just to satisfy curiosity, I use prefixes for things like sequences S_, functions F_, package bodies P_ and packages, views V_, Procedures _R etc).
Constraints have suffixes such as _FK, _CK..., so _UK, and _PK. I notice that it is easy to create a primary or unique constraint with an index of the same name even with the using index clause. The result is an index with suffix _UK, and PK.
However, I was wondering what some opinions are about giving extra attention to (developers, modelers etc) to separate further these types. So all constraints are 'K' and all indexes 'X'. e.g. ( FK, FX), (PK, PX), (UK, UX).
An immediate result that I see is that FK would not need the FK_I , (or even a number for multiples, as it would be simply end in _FX. (NAME_FK, NAME_FX).
The idea of further refining this is somewhat a result of what Tom Kyte says about metadata and indexes, and that metadata is good.
Examples concerning metadata:
A constraint is metadata, an index is the mechanism.
A foreign key can be defined to a unique constraint, but not to a unique index.
This of course takes some extra effort. I think as things get bigger that it is worth it. Any consensus out there?
Example: (leaving out FK).
CREATE TABLE EXAMPLE_TABLE (
PRIM_COL number constraint EXAMPLE_TABLE_PK primary key
using index ( CREATE INDEX EXAMPLE_TABLE_PX ON
EXAMPLE_TABLE(PRIM_COL)),
UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE
using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on
EXAMPLE_TABLE(UNIQ_COL)),
junk varchar2(10)
);
select table_name, constraint_name, constraint_type, index_name
from user_constraints where table_name = 'EXAMPLE_TABLE';
TABLE_NAME CONSTRAINT_NAME C INDEX_NAME
------------------------- ------------------------- ------------------
EXAMPLE_TABLE EXAMPLE_TABLE_PK P EXAMPLE_TABLE_PX EXAMPLE_TABLE EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX
DBMON _at_ COSDEV> select table_name, index_name from user_indexes where table_name = 'EXAMPLE_TABL
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
EXAMPLE_TABLE EXAMPLE_TABLE_PX EXAMPLE_TABLE EXAMPLE_TABLE_UNIQ_COL_UX
Joel Patterson
Database Administrator
904 727-2546
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 10 2011 - 08:28:26 CST