NYCPHP Meetup

[Tim Sailer]

On Tue, Oct 04, 2005 at 12:03:24PM -0400, Dan Cech wrote:
> Allen Shaw wrote:
> > 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 ...).
> 
> I was going to say the same thing, although you would probably want the 
> address table to contain the user id, address type and address columns.
> 
> It seems to make sense from pretty much every angle, and of course will 
> work for any number of different addresses.  If you're interested in 
> doing any queries that would match against more than 1 address then this 
> will also be much easier, like:
> 
> SELECT user_id FROM addresses WHERE address_type IN (1,2,3) AND 
> country='USA'
> 
> If you do go this way there is definitely no need to have any address 
> columns in the user table, which is also a boon for any query where 
> you're not pulling address information (which in my (limited) experience 
> tends to be most of them).

I agree. I also go one step more extreme, and have an address_type table that
has the address_type_id and the char user-friendly 'address type'. This way
(for the benefit of people that haven't done this type of thing before) you
simply add another row to the address_tpe table to add another type of address,
and to use in your contraints. If you're not using DB enforced constraints (ie
foreign keys), be careful to not delete an address type from the table and
leave orphaned records in the address table.

Tim

-- 
Tim Sailer <sailer at bnl.gov> 
Information and Special Technologies Program
Office of CounterIntelligence 
Brookhaven National Laboratory  (631) 344-3001