Re: Modeling Question

Big con - if someone moves house or has more than one address, you can't store details. You can't sort by surname. You can't find people from addresses.

Try this:

Client : 	fld_social	(social security number)
	fld_surname	(surname)
	fld_forenames	(forenames)

Address:	fld_ID		(address ID, eg. autonumbered)
	fld_street		(address line 1)
	fld_location	(address city)
	fld_region	(address state)
	fld_code		(address postal code)

Index:	fld_social	(social security number)
	fld_address	(address ID)

Relationships:	Client -> Index	one-to-many, cascade deletes, updates
		Address -> Index	one-to-many, also cascaded

That should do you. Better yet, hire me to do it for you.

On Thu, 16 Dec 1999 10:05:10 -0600, Steve Lucord <steve.a.lucord_at_lmco.com> wrote:

>I have several tables that contain address information in the system I
>am currently working on. Instead of putting the columns that pertain to
>
>the address in each table, I created an address table with a system
>assigned key. Each database table that needs an address contains a
>foreign key to the seperate address table.
>
>For example:
>
>CLIENT
>
>Social Security VARCHAR2(11) 555-55-5555
>Address_Id NUMBER(15) 1
>Name VARCHAR2(30) Joe Client
>
>ADDRESS
>
>Address_Id NUMBER(15) 1
>Address_Line VARCHAR(80) 123 Address Way
>City VARCHAR(30) Any City
>State VARCHAR(2) AK
>Zip Code VARCHAR(10) 77598-5339
>
>I am looking for the pros-and-cons of modeling the database in this
>manner. Philosophical as well as practical arguments are welcome.
>
>Thanks in advance for any suggestions.
>
>Steve Lucord
>
>

--
Mark A Preston BSc, FIAP
Business Manager, MicroFix Systems Solutions mark_at_mpreston.demon.co.uk Received on Fri Dec 17 1999 - 10:32:13 CST