What data standards should a database designer know about?

Some are so general they jump out at you --

ISO-8601 temproal dispaly formats.
ISO-3166 Country codes
ISO-5218 sex codes
email addresses and URLs
Various national postal codes
Various banking codes like CUSIP and ISIN
ISBN for books
ISAN for AV media
VIN for vehicles
Passport numbers

What am I forgetting? What is a good source for them?

As an aside, a few months ago, a poster assured me that his industry
(metals) had no standards and I should shut up and not criticize him
for "cowboy coding" his own encoding schemes.

Since I could look on the bottom of my cookware and see some numbers
that deal with the kind of stainless steel used, I was pretty sure he
was wrong, so I Googled it (http://metals.about.com/od/specification1/
Metals_Specifications_and_Standards.htm)

I am trying to get a book together on such standards so that maybe the
next batch of Newbies will stop inventing their own encodings.
Suggestions welcome!

--CELKO-- wrote:
ISO 4217 currency names
http://www.iso.org/iso/support/faqs/...des_list-1.htm

This has held a special place for me ever since I was asked to add a
"currency symbol" for every currency. The project manager didn't realize
most currencies don't have a symbol. Nor did he know anything about
encodings and fonts....

ISO 639 language codes http://xml.coverpages.org/iso639a.html

IANA character sets http://www.iana.org/assignments/character-sets
and character data encoding standards e.g. ISO 8859-1.

Rare in my experience is the programmer or DBA who has more than a foggy
notion about encoding. I recently helped someone who wanted to load
"Japanese" who'd never heard of Unicode or even character sets.

Which reminds me: IEEE 754 Floating Point Numbers, e.g.
http://steve.hollasch.net/cgindex/coding/ieeefloat.html.

Whether to store single- or double-precision numbers can materially affect
historical quantitative databases. Understanding precision and scale is
very helpful.

Those come immediately to mind. HTH.

--jkl

On Mar 25, 1:29*am, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
Be aware of the fact that there might be different standards for the
same thing.
So there just might not be THE data standard to use, but you will have
to choose the most appropriate
or the most comprehensive - to be open for further extensions of your
system.

E.g. for sex codes you mentioned ISO-5218.
But there are other standards for this as well, e.g.
- CDC
- NETSS & NCHS & ECML & NCVHS & JC3IEDM & ICAO
- NAACCR
- ANSI ASC X12
- ASTM E1633
- DICOM
- ZIMS
- OBIS
- UBIF

The ISO standard has only 4 values (unknown=0, male=1, female=2, not
applicable=9), so it doesn't cater for other values like
hermaphrodites, transsexual, etc.
See http://de.wikipedia.org/wiki/Datenst...es_Geschlechts
This site is in German only - haven't found an English translation,
but most of the tables contain English values/description.

--CELKO-- wrote:

[snip]

I don't object to these or any other standards in principle, but
if the business itself (i.e. the Enterprise of Interest) uses
different or proprietary standards it will reject the IT department
imposing them. The database is just the business' own account of its
state, using its own world-view and its own terminology.

A handbook of relevant standards would be Good Thing, but don't aim it
at database designers specifically. If database designers find gaps in
the requirements they are given by the business then of course
suggesting an appropriate standards-based solutution does no harm, but
priority has to be given to the data representation the business
itself asks for (right or "wrong").

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

On Mar 24, 6:29*pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
Dangerous Goods Classes (HAZMAT)
http://en.wikipedia.org/wiki/Hazardous_material
www.unece.org

Dennis

I have been able to convince management to go with their industry
standards with the simple argument that if you invent your own you
cannot use external data sources and might go to jail. Here in the
US, the term ROI now means "Risk of Incarceration" and not "Return on
Investment" instead.

I was looking at the Voter Registration system for Texas and it has a
self-reported Ethnicity code. But US 2010 Census is changing them
(again). The Census data is what will be used in Federal lawsuits.
Now they are thinking about changing their forms and the database.

I looked at the Ethnicity codes for the UK; you are worse than us

This is exactly what I am after!

LOL! My favorite horror story was with CARS (Computerized Auto
Resources System or some such). They were a very early auto parts
network. A salesman went up to Canada and sold a national chain store
their services with one little modification. It had to speak French
as well as English.

He came home very proud of the large contract and informed the
programmers to write a "French program" over the weekend. Remember
Pepe Le Pew the French skunk from the Warner Brothers cartoons? You
just put "Le" in front of any English noun and it is French! "Le
Water Pump" for example.

On Mar 24, 8:29*pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
awhile I applaud the effort to gather such a reference together, I'm
not sure that the DB designer is the appropriate party, or at least
the only party, that should be aware of them.

Such things should be considered at the the "system" and "application"
levels as well. But that opens the whole can of worms regarding where
does "database design" fit into the whole structure of designing
usable / workable systems.... In real life I fear that in many cases,
the dba is involved late enough in the process that he/she is
attempting to push such standards "back up" the design chain.