The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Design Question...

I need to store several different addresses for a large number of people.  Each person may have a Present Address, Mailing Address, Subject Property Address. 

Normally I would create three tables for each address type, but isn't it a better idea to have a generic "Addresses" table with an "AddressType" column that defines the type of address it is (Present, Mailing, Subject Property)? 

If any of the address types had specific attributes, then I would create a separate table for them, but since all these addresses are identical in their structure I see no need to have separate tables.

Thinking further on this, when querying this data the single table solution will make it more difficult to extract a person and all their addresses in a single row.  It would require a set of CASE statements for each type.  The multiple table solution would require several joins, but would return one row.

Which is the right way to go here?
zigzag Send private email
Wednesday, October 25, 2006
 
 
No need for a CASE but you will have a bunch of column aliasing (no two ways about that) if you are going to pull it out flat and denormalized (there are two cases shown here depending on how you do your linking):

No types - addresses ARE homogeneous - type is determined by the linker (person)

SELECT Person.*,
ShipAddress.Address1 AS ShipAddress_Address1,
PhysicalAddress.Address1 AS PhysicalAddress_Address1
-- etc.
FROM Person
LEFT JOIN Address AS ShipAddress
  ON Person.ShipAddressID = Address.AddressID
LEFT JOIN Address AS PhysicalAddress
  ON Person.PhysicalAddressID = Address.AddressID

Or typed, with linkage determined:

SELECT Person.*,
ShipAddress.Address1 AS ShipAddress_Address1,
PhysicalAddress.Address1 AS PhysicalAddress_Address1
-- etc.
FROM Person
LEFT JOIN Address AS ShipAddress
  ON Address.AddressType = ADDRESSTYPE_SHIPPING
  AND Address.PersonID = Person.PersonID
LEFT JOIN Address AS PhysicalAddress
  ON Address.AddressType = ADDRESSTYPE_PHYSICAL
  AND Address.PersonID = Person.PersonID

Now there are some issues here in that your person information is repeated over and over, so you might just want two resultsets.

We actually use a three table schema, the person, the address and a linker table.  There are only two (technically three) objects in our object model - a person (1) and a list (2) of address objects (3).
Cade Roux Send private email
Wednesday, October 25, 2006
 
 
Why do you need a separate Address table? Is Person to Address a 1 to *, * to 1 or * to * relationship?

If all you have is a 1 to 1 relationship you may set the address as attributes. Address is functionally dependent on the person therefore it should be in the person table (2NF).

Having a type column in Address is a NON-NF. The 1NF requires atomic entities defined by a single predicate. With a type column you OR many predicates and the entity is non-atomic.

Please provide the list of relationships - in the form:
Person -> Mailing Address
Person ->> Shipping Address
Address ->> Person
...

Then I can have a more detailed answer.
Dino Send private email
Wednesday, October 25, 2006
 
 
Person to Mailing Address is 1..* to 1.
Person to Present Address is 1..* to 1.
Person to Property Address is 1..* to *.

The meaning of the 1..* is that one or more "Persons" can use the address.  As an example, two people can have the same Mailing Address (husband and wife).  None of these addresses are only 1:1 which is why I'm ruling out have the Address as an attribute of Person.
zigzag Send private email
Wednesday, October 25, 2006
 
 
A system behavioral issue which will arise is what happens when you change an address that more than one person is linked to - do you split it or do all entities linked to that address get the new address?
Cade Roux Send private email
Wednesday, October 25, 2006
 
 
"A system behavioral issue which will arise is what happens when you change an address that more than one person is linked to - do you split it or do all entities linked to that address get the new address?"

You don't update but create new. The old address is kept in the history of things if any. Orphaned addresses can be purged by an offline scheduled job.
Dino Send private email
Wednesday, October 25, 2006
 
 
Person to Mailing Address is 1..* to 1.
Person to Present Address is 1..* to 1.
Person to Property Address is 1..* to *.

Person: (PK:person_id, name, gender, ...)
Address (PK:address_id, str, city, state, zip)

With history:
========================
Mailing_Address (PK:[person_id, address_id], last_changed)
Property_Address (PK:[person_id, address_id], last_changed)

The present address can be retrieved by:
select * from mailing_address where last_changed is null and person_id = $1

To change an address, say mailing_address:

1) select or create a new address in Address
2) bind $1 = person_id ; $2 = address_id
3) update mailing_address set (last_changed = sysdate) where person_id = $1 and last_changed is null;
4) insert mailing_address ( person_id, address_id ) values ($1, $2)

Without history:
========================
Mailing_Address (PK:[person_id, address_id])
Present_Address (PK:[person_id, address_id])
Property_Address (PK:[person_id, address_id])

Getting the present address will return many addresses (Person to Present Address is 1..* to 1.) !!!
select  * from present_address where person_id = $1

Updating an address is

1) select or create a new address in Address
2) select an address to update from the set
    select * from mailing_address where person_id = $1
3) bind $1 to person_id $2 old address_id  $3 = new address_id
4) update mailing_address set (address_id = $3) where person_id = $1 and address_id = $2;

Or something like this ...
Dino Send private email
Wednesday, October 25, 2006
 
 
>>Person to Mailing Address is 1..* to 1.
>>Person to Present Address is 1..* to 1.
>>Person to Property Address is 1..* to *.

>>The meaning of the 1..* is that one or more "Persons"
>>can use the address.  As an example, two people can have
>>the same Mailing Address (husband and wife).  None of
>>these addresses are only 1:1 which is why I'm ruling out
>>have the Address as an attribute of Person.

Why does the system -have- to support a single address shared by multiple people? What will the foreign key in the people table look like? (Probably, an abitrary number.)
somebody else
Wednesday, October 25, 2006
 
 
"You don't update but create new. The old address is kept in the history of things if any. Orphaned addresses can be purged by an offline scheduled job."

Sure, but it still doesn't answer the user expectations problem.  There will be usage cases where the user expects both behaviors.
Cade Roux Send private email
Thursday, October 26, 2006
 
 
Single address change is as I described above (I guess). For address across the db:

update mailing_address set(address_id = $new_address) where
address_id = $old_address;


If there is history, then you have:

insert into mailing_address (person_id, address_id)
(select person_id, $new_address from mailing_address where
address_id = $old_address and last_changed is null);

update mailing_address set(last_changed = $date_change) where
address_id = $old_address;
Dino Send private email
Thursday, October 26, 2006
 
 
Ooops ... last statement is more like:

update mailing_address set(last_changed = $date_change) where
address_id = $old_address and last_changed is null;
Dino Send private email
Thursday, October 26, 2006
 
 
My structure would look something like:

Customers
CustomerID
Name
MailingAddressID
ShippingAddressID
...

Addresses
AddressID
CustomerID
AddressType
Street
City
...

Kind of a double-join thing. For the "easy" addresses (the ones you use all the time), you've got a very easy "flat" way to do it: just join on MailingAddressID and ShippingAddressID. For additional addresses or other address types, you'd have to join based on CustomerID, or use some of the other techniques mentioned here.
BradC Send private email
Friday, October 27, 2006
 
 
I hope I'm not chiming in too late.

While letting multiple people use the same address record may improve your database efficiency, it is just going to cause you major headaches whenever someone changes their address.

I'd recommend this, based on experience working with a large purchasing application:

Keep address attributes out of your Person table.
Have one address table with a type column to define address type {billing, shipping, home, office, whatever}. (you could have another 2-column lookup table that "defines" the type by foriegn key constraint if you want to keep bogus type values out of the address table.)

In the address table, each address should have a foreign key to the person table, so 1 person can map to addresses of different types. you could make the primary key of the address table to be the address.personID and address.type, because it is dependent on the person table and (i'm assuming) a person can only have one address of each type.

Now for selecting address records: just return two result sets. select person where id =? and then select from address where personid=? (and type=?).

when you delete a person just delete all the address associated with that person. if a person updates one of their addresses, you don't have worry about messing up the records of other people.

i wouldn't stress out too much on the what-ifs of saving space when two people have the same shipping address...


that's just my two cents. do what you think is right :)
dan m Send private email
Wednesday, November 08, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz