NYCPHP Meetup

[Dan Cech]

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).

Dan