Re: Database naming conventions?

[--CELKO--]

> I always prefix the column name with a prefix that identifies the
table where it resides. <<

No. You name a data element for WHAT it is, not for WHERE it is. Do
you change your name when you move from room to room? Start with any
good book on data modeling and the ISO-11179 Standards.

You make a name as precise as possible -- not vague like "name", "id",
"date" etc. The ISO format is "<attribute>_<property>" --
"customer_name", "product_id", "birth_date", etc.

Get a copy of SQL PROGRAMMING STYLE and use it for your company's
guidebook. I based it on the ISO-11179 data element naming rules and
the research I did for a few years at AIRMICS (Army Institute for
Research in Management Information & Computer Sciences).

If you use my book:
1) I can make my house payment
2) You can save yourself a lot of work
3) If anyone does not like it, you can blame me! Everybody wins!

[Philipp Post]

and the ISO-11179 Standards. <

For those who would like to have a look at it - ISO has several
standards available at no charge here (including 11179)

http://standards.iso.org/ittf/Public...ableStandards/

brgds

Philipp Post

[Marcello]

On 23 jun, 12:00, --CELKO-- <[e-mail]:

You make a name as precise as possible -- not vague like "name", "id",
"date" etc. The ISO format is "<attribute>_<property>" *--
"customer_name", "product_id", "birth_date", etc.

I answered it some hours ago and it dind´t already apperar in
Google,So I´ll ask again(In the case it got lost)
Why Costumer_name and not Costumer_BirthDate?
Lets suppose I have a veterinary hospital and whant to know the name
and date of birth of every pet along with the name and date of birth
of their
owners.
By following your example I would end up with two columns witrh the
same name birth_date.

Thanks,
Marcello

[--CELKO--]

>Why customer_name and not costumer_birthdate? <<

That is fine, but you will need to add "_birthdate" as special case of
property. What you would probably have is a role prefix --
"customer_birth_date" and "pet_birth_date", in the tables.

Again, the basic rule is that a data element name tells us WHAT it
is. It does NOT tell us about
1) its locations in the schema
2) how it is used in a particular table (i.e. no PK- or FK- or VW-
affixes in the names
3) how its data is physically stored (i.e. no "i-", "str-" etc.
affixes. for integers, strings, etc).
4) Do not tell us what the data element is not.

[Arved Sandstrom]

"Marcello" <md*@ibest.com.brwrote in message
news:ea**********************************@26g2000h sk.googlegroups.com...
On 23 jun, 12:00, --CELKO-- <[e-mail]:

You make a name as precise as possible -- not vague like "name", "id",
"date" etc. The ISO format is "<attribute>_<property>" --
"customer_name", "product_id", "birth_date", etc.

I answered it some hours ago and it dind´t already apperar in
Google,So I´ll ask again(In the case it got lost)
Why Costumer_name and not Costumer_BirthDate?
Lets suppose I have a veterinary hospital and whant to know the name
and date of birth of every pet along with the name and date of birth
of their
owners.
By following your example I would end up with two columns witrh the
same name birth_date.

Thanks,
Marcello

**************************************************

I wouldn't myself get too twisted up about this. When you're devising column
names you're frequently answering the question "what property of what
entity?" So "name of the customer" and "id of the product" naturally
translate into "customer_name" and "product_id". In your example it's
actually "birth date of the pet", not just "date of birth", because the
latter still begs a question. So something like "pet_birthdate" might be
better if following these conventions.

The distinction comes about because "name" and "id" actually mean
something - they are not just data types. Whereas "date" is a data type.
Further qualification leading to something like "date of birth" or "start
date" introduces real meaning.

AHS

[Marcello]

On 23 jun, 17:19, --CELKO-- <[e-mail]:

Why customer_name and not costumer_birthdate? <<

That is fine, but you will need to add "_birthdate" as special case of
property. *What you would probably have is a role prefix --
"customer_birth_date" and "pet_birth_date", in the tables.

But I can think of many other "special cases" like date of
issue,gender and many more.
And If i fail to define a "special case" during the design of the
database,what should I do later,come back and change the field names?

Many advocate,that you should define just birth_date in both tables
and give aliases to the column names for every sql statement.
As i understand you said we should look for "special cases".
Others like to prefix column with table that they belong.
I just didn´t find a perfect schema,I don´t like the idea of using
something just because it is an ISO Standard.

Thanks,
Marcello

[Marcello]

I´ll try to redesign a database I´m desiging right now,following the
iso standards.
In fact it seems to have more pros than cons.
In the vast majority is just replacing te table prefix by the table
name(Although in teory it is not that),but it seems to be more
understandable,since prefixing every table is an annoying job.
It will produce much bigger sql statetements but easier to read in the
overall.

customer_birth_date instead of Cust_BirthDate
pet_birth_date instead of Pet_BirthDate

Thanks,
Marcello

[--CELKO--]

>Many advocate,that you should define just birth_date in both tables and give aliases to the column names for every SQL statement. <<

Data element names are not just for SQL; they are in a Data Dictionary
and everyone should use them.

>I don´t like the idea of using something just because it is an ISO Standard. <<

Using something because it is an ISO Standard is a VERY good reason.

That is the way that Americans felt about the Metric system :) That
is why we no longer dominate the automobile industry, etc.

[Philipp Post]

Marcello,

But I can think of many other "special cases" like date of issue,gender and many more. And If i fail to define a "special case" during the design of the database,what should I do later,come back and change the field names? <

Unfortunately this can happen. However if you took enough time in the
design phase, you could minimize the possibility that it does happen.
It is a bit like chess - asking yourself "Could it happen that I have
to add functionality lateron what will lead to duplicate column
names?". If you have the possibility to change the column names, yes I
would do so, but this usually is a time-consuming task as you have
views, procedures and front-end code what refers to the old column
name.

Rushing in the design phase creates workload lateron in x-degree of
the time you saved by quickly passing through design.

brgds

Philipp Post

[Sybaseguru]

Rushing in the design phase creates workload lateron in x-degree of

the time you saved by quickly passing through design.

Quite agree.

After designing relational databases for 33 years:-

I don't like the "_" method - eg "birth_date" as it adds an extra character
which adds little to the meaning.

My preference is for "BirthDate" - mainly because its tends to cause less
problems with reserved words in processing languages, (eg I would use
$BirthDate in perl processing) and its easy to spot if you use all
lowercase (typically sqlserver or sybase) or uppercase (typically Oracle)
for the sql.

Sometimes you have to break the rules - denormalisation is a common culprit.

If you have the same named column in two tables it MUST mean the same
thing - so if you use BirthDate in the Customer table, then in the Animal
table (to use your vet example) you must use something different for the
Animal birth date. However if the Customers BirthDate is part of the
Customer key (Maybe you differentiate Smiths by their date of birth), then
you might well have BirthDate in the Animal table to provide a join with
the Customer table. If this is the case the 'CustBirthDate' is a sensible
name to differentiate from 'AnimalBirthDate'.

As an aside I like to have a list of 'standard' abbreviations I use so in
this case I would have used DoB for BirthDate.