Re: Modeling Question

Add an addresstype field to the table if you expect a non-address entity to be able to refer to more than one address, and a join table to manage it. Say you have a Customers table and an Address table, then make a CustomerADdress table that has CustomerID+ADdressID, with a unique index on AddressID to enforce no multiple customers to same addressID, or store the CustomerID with the address record (not my first choice), with referential integrity (Primary Key/Foreign Key relations) set up, of course.

If your database needs only to tie people to addresses, then it's up to you. But if you have to tie other things (buildings, companies, etc.), that could be stored in other tables and related to addresses, then I would store the addresses in a separate table, unless it really was clear that one address needed one kind of format an another address needed something entirely different, and you don't want to have a bunch of null fields in each address record...

I did this in a contacts database at my old job. While it meant that for our organization there was a good chunk of people with the same address, this could just end up being a client application design issue (make it default the most common address, or have an internal list of default addresses). If you don't have multiple entities referring to a single address, then managing deletions is easy with cascading deletes or triggers... It is ugly if you have multiple entities referring to a single address.

-Corey Lawson
 corey.lawson_at_worldnet.att.com

Peter van der Hoogte wrote:
>
> Hi Steve,
>
> It is a good design principle to store the same information only once in
> your database. Your design has this quality. It will be easier to maintain
> integrity for your data and you will have to do less programming.
> Storing the same information in more then one place, means your will have a
> problem keeping al those instances of the same adres consistentent if it
> changes. You also have to make functionality to enter, display and print
> addressen more than once.
>
> But a lot of organisations do not store adresses seperately, because
> adresses are closely tied to a person or organisation. And from a
> programming point of view it is easier if the data is in the same table.
>
> So there is no easy answer.
>
> Your design also allows addresses to be re-used. Is this what you want? Or
> should an address always belang to exactly one person or company? Allowing
> reuse to take place creates its own problems.
> And your design does not allow for more then one address for each person/
> company. Is this what you want. Usually companies also have a separate
> address for correspondence.
>
> Greetings,
>
> Peter
>
> Steve Lucord 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
Received on Mon Jan 03 2000 - 00:25:36 CST