ISO-11179 makes my head hurt  
Author Message
rmacdougall





PostPosted: Wed Jun 16 10:32:27 CDT 2004 Top

SQL Server Developer >> ISO-11179 makes my head hurt

I'm doing my level best to adopt the naming conventions outlined in
ISO-11179 and expounded by Joe Celko, but moving from theory to practice has
me somewhat confused. I was hoping for some feedback on the following SQL2K
tables:

CREATE TABLE [HR_Personnel] (
[employee_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[middle_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [PK_HR_Personnel] PRIMARY KEY CLUSTERED
(
[employee_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [EDU_Residents] (
[employee_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[division_name] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[clinical_year_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_EDU_Residents] PRIMARY KEY CLUSTERED
(
[employee_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_edu_resident_hr_employee] FOREIGN KEY
(
[employee_id]
) REFERENCES [HR_Personnel] (
[employee_id]
)
) ON [PRIMARY]
GO

CREATE TABLE [EDU_Logs] (
[employee_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[activity_date] [smalldatetime] NOT NULL ,
[activity_code] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[hours_quantity] [tinyint] NOT NULL ,
CONSTRAINT [PK_EDU_Logs] PRIMARY KEY CLUSTERED
(
[employee_id],
[activity_date]
) ON [PRIMARY] ,
CONSTRAINT [FK_edu_log_edu_resident] FOREIGN KEY
(
[employee_id]
) REFERENCES [EDU_Residents] (
[employee_id]
)
) ON [PRIMARY]
GO

Context:
The above will be used in an application to allow medical residents, i.e.
doctors in training, to log their clinical activity hours.

Questions:
1. In the HR_Personnel table, should last_name be employee_last_name? I'm
not sure whether the table name counts as the object class term or not.
2. Are the HR/EDU table prefixes kosher? I'm using them as namespaces to
allow for future growth.
3. Should EDU_Logs be renamed to something more meaningful? I'm almost
positive the answer is yes. Perhaps EDU_Activity_Logs or
EDU_Activity_History? Maybe EDU_Resident_Activity_History? At this point I
really have no idea.
4. EDU_Logs.hours_quantity sounds weird. Scalars are supposed to be composed
of singular terms. Any suggestions?
5. Are there any online resources with concrete examples of how to apply the
ISO-11179 standard to database nomenclature?
6. Am I being too anal? Should I just settle on a naming convention and go
with it?

Thanks for taking the time to read this.

Sincerely,
Chris Hohmann

SQL Server203  
 
 
Anith





PostPosted: Wed Jun 16 10:32:27 CDT 2004 Top

SQL Server Developer >> ISO-11179 makes my head hurt Chris,

I somewhat concur with the 6th point in your post, as long as you have your
naming convention documented well. However one could use the ISO standards
as a good reference in coming up with a new one.

"Life is really simple, but we insist on making it complicated." ~Confucius

--
Anith


 
 
Joe





PostPosted: Wed Jun 16 12:59:14 CDT 2004 Top

SQL Server Developer >> ISO-11179 makes my head hurt >> 1. In the HR_Personnel table, should last_name be employee_last_name?
I'm not sure whether the table name counts as the object class term or
not. <<

Are their last names logically different from a mere "last_name"?
Probably not. One of the principles is to name something for what it
is; not for how or where it is used in a particular table.

>> 2. Are the HR/EDU table prefixes kosher? I'm using them as namespaces
to allow for future growth. <<

Are "HR_Personnel" logically different from mere "Personnel"? If so,
how? The concept of a name space is part of storage and representation,
not the natrure of the data.

>> 3. Should EDU_Logs be renamed to something more meaningful? I'm
almost positive the answer is yes. Perhaps EDU_Activity_Logs or
EDU_Activity_History? Maybe EDU_Resident_Activity_History? At this point
I really have no idea. <<

Is the term "EDU_Logs" well understood in your environment? Does it need
to be more specific? Probably a little specific would be better, but I
am guessing. I am prone to say "_History" for such tables, so I know
that they have temp****components.

>> 4. EDU_Logs.hours_quantity sounds weird. Scalars are supposed to be
composed of singular terms. Any suggestions? <<

Does "hours_spent" or "time_used" sound better? Hours is a unit of
measurement, so it feels bad in a data element name.

>> 5. Are there any online resources with concrete examples of how to
apply the ISO-11179 standard to database nomenclature? <<

Whitemarsh consulting was working on metadata tools based on ISO-11179
and there is a short discussion of some examples in the specs
themselves. But I don't know of a general tutorial -- not a bad idea,
tho!

>> 6. Am I being too anal? Should I just settle on a naming convention
and go with it? <<

How can anyone be too anal? Remember who you are talking to :)
Consistent is better than "roll your own", of course. But think of the
naming conventions as part of the whole development process.

When you set up the data dictionary, you can make a list of affixes and
how to use them ("_code" means an established standard exists for this
attribute and the data dictioanry must tell you about the trusted
external source for it. "_id" means an identifier for a unique entity of
a certain kind which must be verifiable. Etc.)

The nice part of this is that your new developer can sit down and figure
out if something already exists because he can have a good idea as to
what it is called. He can then go to data dictionary and use it. No
more worries about someone using ZIP and someone else using ZIP+4 in
different tables. They go to "postal_code", find the preferred industry
name the company uses and the format.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.hide-link.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
Chris





PostPosted: Wed Jun 16 14:35:59 CDT 2004 Top

SQL Server Developer >> ISO-11179 makes my head hurt Thank you so much! It really is a treat to be getting it from the source. :)
I pulled an all-nighter last night so hopefully I'll be more lucid once I
get some sleep. But in reading over your reply and your other posts on
ISO-11179 via a google groups search, I believe I'm beginning to see a light
at the end of the tunnel. The "<genus><species>" concept is starting to make
more sense. Thought you might like to know that I've also abandoned the
IDENTITY "thing". I'll be eating all "natural" from here on out. :)


 
 
Rickard





PostPosted: Wed Jun 16 16:04:10 CDT 2004 Top

SQL Server Developer >> ISO-11179 makes my head hurt

> I'm doing my level best to adopt the naming conventions outlined in
> ISO-11179 and expounded by Joe Celko, but moving from theory to practice has
> me somewhat confused. I was hoping for some feedback on the following SQL2K
> tables:

How did you obtain the standard? Some book or downloaded document from
ISO themselves?

> 1. In the HR_Personnel table, should last_name be employee_last_name? I'm
> not sure whether the table name counts as the object class term or not.

Doesn't this depend on whether last_name is a unique column name or not?
If you have other things with last names, perhaps external contacts, you
would have to use employee-last-name. That's how I understand the
standard, but I might be wrong.

> 2. Are the HR/EDU table prefixes kosher? I'm using them as namespaces to
> allow for future growth.

If your business treat HR-Employees different than Employees or
Whatever-Employees than yes IMO.

Rickard