Re: Modeling Question

Mark Preston <mark_at_mpreston.demon.co.uk> wrote in message news:385a6219.731449_at_news.demon.co.uk...
> 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
>
> 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.

All depends on the business needs I suppose. I can't see why you couldn't find people from addresses with the model suggested. Anyway, I do agree with your idea below, but would add some extra fields...

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

I would add: ADDR_TYPE, START_DATE, END_DATE ...to better model what you suggested. The dates would enable you to have the history of changes to addresses, and how long they've been at their new one, etc. And of course the type: customer, billing, copy, alternative, etc.

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

Good one! I wish I could move around that easily.

Cheerio Received on Fri Dec 17 1999 - 18:08:06 CST