| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: opinions on naming primary keys in new database
Ron,
I think Hugh was asking about column names, not the actual constraint names.
You have a valid point, the constraint name should be more descriptive, with the table name and column in it, followed by 'pk', 'fk', etc. The subject of the e-mail is a bit misleading and probably should be "opinions on naming primary key COLUMNS in new database" because after reading his e-mail, I think that he was talking about column names and not constraint names.
Jeff
-----Original Message-----
Sent: Thursday, March 08, 2001 9:40 AM
To: Multiple recipients of list ORACLE-L
Hugh,
For ease of maintaining the indexes ,keys and the space that they consume,
in this shop they were named with the convention table_column_pk ,
table_column_ix , table_column_fk with numeric indications when there were
more than one. It makes it easier to see which index is growing and identify
the table associated with the growth. If all of the pk's were called "id"
etc, how would you know where the actual growth was occurring.
2 bits.
ROR mª¿ªm
>>> jeff.cox_at_ips-sendero.com 03/08/01 11:10AM >>>
Hugh,
You are right that Richard Barker says not to use an entity name as part of an attribute name. (CASE*METHOD Entity Relationship Moddelling, by Richard Barker, page 3-9) And I believe Oracle still gives out this book in their Data Modeling class.
I have seen databases that completely follow this method and others that include the table name in every column. As far as naming your primary key as a column called 'key', I don't like it either.
I am also curious to see what is the preference of other list members.
Regards,
Jeff Cox
IPS-Sendero
Scottsdale, AZ
-----Original Message-----
Sent: Wednesday, March 07, 2001 4:58 PM
To: oracledba_at_lazydba.com
Hello,
I wanted to get the lists opinion on the preference of naming primary keys.
Please see my example below with the CUSTOMER table, and the customer TYPE table. The column 'type_id' is a foreign key referencing the TYPE table. This is just an example to get opinions.
CUST TYPE +---------+ +------+ | id | |id | | name | |desc | | type_id |>------| | +---------+ +------+
I like having my primary keys called id, and not including the table names in the columns of the original table. For example, in the CUST table, we would not have 'cust_id' as the primary key, we would just call it 'id'. But having 'type_id' as a column of the CUST table is okay because that is a foreign key, and not an attribute of the original CUST entity.
Including the table name in a column that is not a foreign key, I believe, is redundant and not necessary. (I also remember reading this in a Richard Barker book.)
All foreign keys would then have the standard of table_column, with the exception of tables with multiple foreign keys from the same table, and recursive relationships - which would then just include a more descriptive table_column name.
This way, when you do a describe on a table, you will immediately be able to tell what are the foreign keys, and the primary keys. (I also believe in surrogate keys for most tables, so the problem of having a composite key is not an issue here.) I think that this will later make the database easier to understand to new DBAs and duhvelopers, which would have been nice when I worked on my first database.
My questions:
All constructive criticism is welcomed.
Thank you,
Hugh
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Cox INET: jeff.cox_at_ips-sendero.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: RROGERS_at_galottery.org Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Cox INET: jeff.cox_at_ips-sendero.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Mar 08 2001 - 12:17:08 CST
 |
 |