We have strict algorithm for table columns, keys, sequences and indexes
1. All tables have 3 cahracter long aliases
So all columns are in form <alias>_<colname> for example emp_id, emp_name,
emp_adr_id, adr_street_name
2. All tables have surrogate primary key column in form <alias>_id
3. All primary keys are named <alias>_pk
4. All foreign key columns are in form <alias1>_<alias2>_id
5. All foreign keys are named <alias1>_<alias2>_fk
6. If there are more than one foreign key to another table then foreign
keys are named <alias1>_<alias2>_<meaningful_name>_fk
6. All unique keys are named <alias>_<meaningful_name>_uk
7. Every table has its own sequence in form <alias>_seq
8. Every index on foreign key is in form <alias1>_<alias2>_fk_i
9. Every index on one column is in form <full_column_name>_i
10. Every index on two or more columns is in form <alias>_<meaningful_name>
_i
The advantages are that one can surely identify object type, object
relation with table, not use aliases in almost all selects (aliases must be
in self joins).
Disadvantage - at least one - longer column names
I don't think it is invention of our organization, for example, Oracle
Designer uses almost the same scheme
But I think every apprach is good enough if it isn't haotic and You are
satisfied with it
Gints Plivna
"Jeff Cox"
<jeff.cox@ips-se To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
ndero.com> cc:
Sent by: Subject: RE: opinions on naming
primary keys in new database
[EMAIL PROTECTED] (seriously)
01.03.08 18:10
Please respond
to ORACLE-L
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: [EMAIL PROTECTED]
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:
1) What is your preference with primary and foreign keys - if you could
design a database from scratch?
2) Am I off my rocker thinking that this is a good way to design a
database?
3) We have a developer who wants to name all of our primary keys, 'key',
and
I am trying to convince her that we should use 'id' or 'nbr' instead. Has
anyone ever used 'key' as the primary key of a table? This seems like it
would be confusing when
All constructive criticism is welcomed.
Thank you,
Hugh
--------
Think you know someone who can answer the above question? Forward it to
them!
to unsubscribe, send a blank email to [EMAIL PROTECTED]
to subscribe send a blank email to [EMAIL PROTECTED]
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeff Cox
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
