NYCPHP Meetup

[Phil Duffy]

Allen,

> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
> On Behalf Of Allen Shaw
> Sent: Tuesday, October 04, 2005 10:54 AM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] data modelling vs. db design (was: ER Diagram
> tool for MySQL/OS X)
> 
> Stephen Musgrave wrote:
> 
> >This is an interesting topic because I'm approaching a question based
> >upon this principal.  There is an application that I am building where
> >the User record can have 5 addresses (home address, work address,
> >permanent address, international address, etc, etc).  I'm considering
> >making a table called UserAddress and then linking it to address ID
> >fields in the User table.  I'm on the fence about it because while I
> >don't want a monstrous User table with tons of columns, I also don't
> >want to over normalize.
> >
> >
> Here's a great working example to explore the issue.  I would let the
> User table contain no address info, and then let the UserAddress table
> contain one address per row with an extra column for UserID.  This seems
> like a clear one-to-many relationship between a person and his/her many
> addresses (also assuming it could be any number between 0 and 5,
> right?), so adding 5 sets of columns to the User table doesn't seem
> right...
> 
> Honestly, the only reason I'm writing here is so someone can correct me
> if I'm wrong (and if I'm right then this might actually helping somebody
> -- neat-o ...).
> 

In my opinion, you are exactly correct.  We went down this road with our
Zephyr System and specifically its Industry-neutral Core
(http://www.eleccott.com/indneutl.htm ).  We also established an Address
Type table and coded each address by type, e.g., Legal Address, Billing
Address, Remittance Address.  One of the attributes of the Address Type
table was a Single-Address-of-Type flag such that only one Legal Address
could be entered, but multiple Vacation Addresses could be entered.

I also believe that creating a database with a specified number of 5 or any
other number of addresses brings unnecessary rigidity, and perhaps
complexity, to a system.

BTW, in the world of business objects, even the concept of a User needs to
be rethought.  I would do away with Users as a fundamental business object
and replace it with the more generic Person, and identify system Users as a
subset of the Person universe.  Users then become Persons who play the Role,
System User, in which additional sets of information are required such as
username, password, permissions, et cetera.

HTH

Phil