ISO Compliance discussion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

ISO Compliance discussion

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-21 : 09:38:55

I will start off by saying that I am no fan of Joe Celko's tone towards many of the newbie programmers (and those of us who have been around a while too) when it comes to ISO standards. I have to admit though that his posts have stirred debate within myself as to how I have been writing code for the past 10 years.

I wanted to show you how I would set up a typical set of tables and wanted your feedback on what I should change to become more ISO compliant.

Joe, if you are reading this I have my flame suit on and am ready for your worst! Again, I have been put off by your tone in the past but I also find myself strangely pulled toward writing more ISO compliant code. So to better myself and my code, I welcome your input.

(I am not bothering with the actual script, just the layout of how I would normally do a set of related tables)

tblPerson would be the table name
Columns:
intPersonID int Not Nullable IDENTITY(1,1),
txtFirstName varchar(100) Not Nullable,
txtLastName varchar(100) Not Nullable,
txtMiddleName varchar(100) NULL,
dtiDateOfBirth datetime Not Nullable,
intGenderID int Not Nullable(foreign key to a lookup table of Genders named "xtblGenders"),
...other columns too but those are the major ones

tblEmployee is my next table
Columns:
intEmployeeID int Not Nullable IDENTITY(1,1),
intPersonID int (foreign key back to tblPerson),
txtEmployeeNbr varchar(100) NULL,
dtiStartDate datetime NULL,
dtiEndDate datetime NULL

So there is a basic layout of two related tables using column types that I use most often. What would you change?

Thanks everyone!!

Hey, it compiles.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 09:57:37

Don't know about the ISO compliance, but I would remove the prefixes on the tables and column names. Especially Hungarian notation, sorry, but that's been outdated for 15+ years.

I'm all for standards and consistency, but I wouldn't hold ISO up as the paragon of standardization. I've found errors in ISO documentation, and they seem more interested in charging people to get the standard rather than following one.

My personal feeling about Joe's posts - and I love the guy - is to stop talking about ISO-whatever-standard and post an example of the standard, why it's better than the user's current design/conventions, and explain some of the problem the standard addresses. Blind adherence to any standard is a recipe for disaster.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-21 : 10:19:34

I agree. Nothing is more deflating than to be told to go do research on ISO-1039384849230 (or whatever) and not give an example to back it up.

So you would go with tables Person and Employee? Drop the "tbl" part? I can live with that, but I do like distinguishing between my lookup tables and my "core" tables. What are your thoughts?

Hey, it compiles.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 10:33:13

Person
identity = Person_id
As Rob says - remove the prefixes. It's just an identifier and should contain information about the datatype.

Ignore the way Joe says things - he is usually correct about the theory - but often not so accurate when trying to apply it specifically to sql server. It's a mistake to miss out on the information because of the tone.

p.s. it's not null rather than not nullable but I like the way you are explicit.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 10:38:07

lookup table I often append with _lkp but it depends on the system.
conjoint tables (many to many joins) I tend to concatenate the tables with an underscore

player - player_id
match - match_id

player_match - player_id, match_id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 10:55:42

I've become a big believer is schema objects. If you look at AdventureWorks you'll get a good idea how they can be used. They have HumanResources, Sales, etc. to delineate seperate "areas" of the database. If you want to remove prefixes and use different schemas to separate or classify tables that would be the way I'd go.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-21 : 11:08:01

quote:
Originally posted by nigelrivett

p.s. it's not null rather than not nullable but I like the way you are explicit.

I know.

Love the critique!

Hey, it compiles.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-04-21 : 12:53:56

I'm a newbie to designing but...

I would use the DATE data type instead of datetime for:
DateOfBirth
StartDate
EndDate

In my opinion rarely does anyone know or enter the time for these, plus it uses less space & makes queries on the columns easier.

On Joe, when I first started on this forum I was like what a ...
but after a while I see where he's coming from, I think.
Here's my guess. Those that worked on older databases (mainframes with COBOL, etc.) had to convert their databases from that to RDMS, but didn't learn RDMS design, so they stuck with what they knew best. And those designs have been passed on & morphed. Joe is just trying to get those designs flushed out & I think he just got tired of saying his opinion nicely like most posters do on this forum. Either that or a stripper (saw this on one of his profiles) he met didn't follow ISO standards

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-21 : 14:01:35

Thank a lot Sqlraider! My co-workers wondered why I just busted out laughing!

And regarding the datetime type that is an excellent example of what I am talking about. I am used to using the datetime type. But if the Date type is a better way to go then I think I need to adapt.

Hey, it compiles.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-04-21 : 16:54:15

Something my shop has started to "standardize" on is naming foreign key columns the same as the name of the reference table. Wasn't really keen on the idea at first, but it's growing on me. I can tell at glance what table(s) is/are referenced, although I'm sure this is violating some standard somewhere!

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-25 : 08:51:31

I prefer that method too BruceT. Of course, that is also why I like prefixing my columns with "int" or "txt". That way I can just glance at the column name and know right away what sort of data to expect. Makes coding the front end easier if you ask me.

Hey, it compiles.

dportas
Yak Posting Veteran

53 Posts

Posted - 2011-04-26 : 08:49:19

Looks like there are plenty of things worth criticising / reconsidering here. Firstly and most obviously if this is a question about standards then why not post some standard data model notation or valid DDL? Without it we have to guess at what is meant by your outline of the table.

Let's assume that your description is complete and that the things you haven't mentioned aren't there - keys and constraints for example! Where are the keys in each table? Do you really allow duplicate employee numbers? What does a null employee number mean? Why is middle name nullable? Does a null middle name mean something different from a zero-length string for a middle name (the latter presumably being what you would want for a person without a middle name)? Why do you allow EndDates without a corresponding StartDate? Do you really want to allow birth dates from the year 1753 to 9999?

Naming: Microsoft and practical everyone else deprecated Hungarian notation about a decade ago. What is its purpose here? Hungarian is expensive, unnecessary, causes errors and maintenance problems and is plain hard to read. First / Last name are ambiguous terms that are best avoided because either given name or surname can be first or last depending on the culture of the person being named. ISO/IEC 11179 has some excellent guidelines on data element names which you might find useful.

Data types: DATETIME is deprecated (and non-ISO standard). What metadata standard prescribes 100 characters for people's names?

Just my 0.02.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-26 : 09:04:07

Thanks dportas, I appreciate the feedback. To answer some of your questions, there were no standard data models when I was "growing up". We had a basic outline of how table names were to be named, but whether a last name field held 10 characters or 239 characters was up to the programmer.

This frankly is why I posted this. I have been doing things for the past 10 years this way because there was no apparent need to change. Whether or not it was a best practice or fit into a standard was not on my mind. But as I have grown I think it is important for me to begin seeing more of the ways others are doing things, especially as it pertains to standards.

I will do some reading on 11179 for data element names. Thanks for the input.

Hey, it compiles.

dportas
Yak Posting Veteran

53 Posts

Posted - 2011-04-26 : 10:06:41

quote:
Originally posted by ajthepoolman

Thanks dportas, I appreciate the feedback. To answer some of your questions, there were no standard data models when I was "growing up". We had a basic outline of how table names were to be named, but whether a last name field held 10 characters or 239 characters was up to the programmer.

This frankly is why I posted this. I have been doing things for the past 10 years this way because there was no apparent need to change. Whether or not it was a best practice or fit into a standard was not on my mind. But as I have grown I think it is important for me to begin seeing more of the ways others are doing things, especially as it pertains to standards.

A lot may depend on the environments you have been exposed to. In large enterprises with complex data management needs interoperability, standards and data governance are often a very big deal. If every different system uses different types and sizes for the same pieces of data based on the whim of one programmer then the costs of integrating, sharing and ensuring consistency of data become much greater.

Ten years ago was a different world. Legal and regulatory compliance and risk management is a much higher priority for many organisations today than it was in 2001. Systems and data are more complex and need to interoperate and share data more frequently. The art and practice of programming has moved on considerably - especially in the Microsoft world where tools and software engineering skills are vastly more mature than they were a decade ago.

madlo
Starting Member

41 Posts

Posted - 2011-05-06 : 11:14:39

Not sure about databases but code compliance checkers for programming code e.g. Fxcop do not like capital letter abbreviations. Not sure if this applies to SQL but if it does you would:
PersonID to PersonId
GenderID to GenderId.

EmployeeNbr is an interesting one. I have seen EmployeeNo, EmployeeNr and EmployeeNumber.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 12:01:29

In my opinion, there are only two things that matter when it comes to naming conventions:

1. It should make sense in context
2. Be consistent.

Everything else is preference. I have my preferences, others have theirs. But if I'm scanning your stored procedures and can't make sense of 'em without constantly referring back to the table DDL, then I have a problem with it.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-05-06 : 14:42:30

quote:
Originally posted by russell

In my opinion, there are only two things that matter when it comes to naming conventions:

1. It should make sense in context
2. Be consistent.

Everything else is preference. I have my preferences, others have theirs. But if I'm scanning your stored procedures and can't make sense of 'em without constantly referring back to the table DDL, then I have a problem with it.

Amen to that. Nothing irks me more than:

DECLARE @fn as varchar(100)

Yeah it is more typing to put it as @firstName, but come on, real people have to read this stuff someday. A small clue as to what the variable or column is holding would be nice.

Hey, it compiles.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-05-09 : 11:46:50

quote:
Originally posted by madlo

Not sure about databases but code compliance checkers for programming code e.g. Fxcop do not like capital letter abbreviations. Not sure if this applies to SQL but if it does you would:
PersonID to PersonId
GenderID to GenderId.

EmployeeNbr is an interesting one. I have seen EmployeeNo, EmployeeNr and EmployeeNumber.

My shop has standardized on proper case (eg. ThisColumn instead of Thiscolumn) and getting rid of redundant naming conventions (eg. we use Employee instead of EmployeeNo, or Person instead of PersonId. We're also trying to limit the use of underscores except when readability is at issue. So far it's working fairly well. Everyone seems to be ok with it (no real personal preference problems) and after refactoring a bunch of old stuff the code is much more readable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources