Posted by Peter van Schie on 11/11/06 11:36

Hi sTony,

> I'm totally new to SQL, and I'm wondering what any of you do about storing
> addresses. It seems wasteful to store the address information as strings,
> but I'm having troubles deciding which is a good approach. This is what I
> came up with.
>
> CREATE TABLE Address
> -- For storing a users address.
> (
> id int(7) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
> user int(7) UNSIGNED NOT NULL, -- references the User Table.
> apt varchar(6) NULL DEFAULT NULL,
> lot varchar(6) NULL DEFAULT NULL,
> street int (7) UNSIGNED NULL DEFAULT NULL,
> city int(7) UNSIGNED NULL DEFAULT NULL,
> state int(7) UNSIGNED NULL DEFAULT NULL,
> country int(7) UNSIGNED NULL DEFAULT NULL,
> zipcode int(7) UNSIGNED NULL DEFAULT NULL,
> PRIMARY KEY(id)
> );
>
>
> It will work, I think, but it has some drawbacks. Most notable is that if
> two users share the same address, the information becomes redundant. Also,
> what if I later want to store a work address and a mailing address, as well
> as a home address? Can anyone offer some insight. I could really use it.
>

Just make a many to many relationship and you solve both problems. To
accomplish that make an extra table like:

users_has_addresses
===================
user_id (int(7)) | address_id (int(7))

Then remove the user column from the address table and you're all set.

HTH.
Peter.
--
http://www.phpforums.nl

[Back to original message]