Posted by sTony on 11/11/06 10:10

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

CREATE TABLE Country
-- A list of Countries referenced by table Address
(
id int(6) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
name varchar(30) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE State
-- A list of States or Provinces by country, referenced by table Address.
(
id int(6) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
country int(6) UNSIGNED NULL DEFAULT NULL,
name varchar(30) NOT NULL,
PRIMARY KEY (id,country)
);

CREATE TABLE City
-- A list of cities by state, referenced by table Address.
(
id int(6) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
state int(6) UNSIGNED NOT NULL,
name varchar(30) NOT NULL,
PRIMARY KEY (id,state)
);

CREATE TABLE Street
-- A list of streets by city, referenced by table Address.
(
id int(6) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
city int(6) UNSIGNED NOT NULL,
name varchar(30) NOT NULL,
PRIMARY KEY (id,city)
);

CREATE TABLE ZipCode
-- A list of ZipCodes by Street, referenced by table Address.
(
id int(6) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
street int(6) UNSIGNED NOT NULL,
name varchar(30) NOT NULL,
PRIMARY KEY (id,street)
);

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.

Thanks in Advance,

sTony.

• Next in forum: Re: Address Table??
• Prev in forum: status display
• Thread view: Address Table??

[Reply to this message]