NYCPHP Meetup

[Phil Duffy]

> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
> On Behalf Of cliff
> Sent: Wednesday, October 05, 2005 11:11 AM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] data modelling vs. db design (was: ER Diagram
> toolfor MySQL/OS X)
> 
> I just read about normalizing to 3NF and as an example, the book showed
> how
> in an extreme case of obsessive 3NF compliance city and state address
> fields
> could really be separate tables since city depends on state, state depends
> on country, etc. A bit extreme to me, but food for thought.

I suspect there are two issues here: (1) storage of codes for address fields
and (2) the overall design requirements of the system.

Typically codes are stored and are in general use outside of systems for
states, e.g., NY for New York and PA for Pennsylvania.  So the question
comes down to the use of codes for further political sub-divisions
(particularly municipalities in the US where counties do not appear in the
typical address).  So it would seem to be obsessive to store the
municipality as a code as opposed to the string, because that would double
the number of disk accesses (one for all the other elements of the address
and one for the municipal code lookup).

The second issue is concerned with the requirements of the system.  I would
make a distinction between 'application' requirements and 'system'
requirements, but I would acknowledge that others could make the case they
are the same.  To me an application tends to be a broad set of functions
accessing a database that address a specified need, such as an airline's
reservation application or an inventory control application.  The
immediately perceived needs define the application.  The system, on the
other hand, tends to be something quite different, what evolves over time,
particularly when the initial application has been successful.

The challenge of the system designer is to discern the future direction of
the system and make no mistakes in the design that would be prohibitive to
reverse.  Application programs are typically not nearly as disastrous as
databases in this regard.

All things being equal, it is probably better to err on the side of
obsession when designing a database.  For example, one approach might be to
use municipal tables to assure that the municipality is a valid entity, but
then store the string.  In that way the database design is normalized at the
foundation level, but has a thin veneer of de-normalization over the top for
operational efficiency.

At least that seems to have worked for me.

Phil