Re: Question about database design

A second address table is definitely the way to go (the '*' signifies the primary key): People Table ============ *user_id first_name last_name etc Address Table (compound primary key) ============= *user_id (fk to People Table) *address_id *obs_no (you can skip this if you don't want to keep an address history) active_ind (is the row currently active or "deleted"?) effective_date expiry_date address_line_1 address_line_2 city etc So say a user lives in the north from Mar to Sept and in the South from Oct to Feb, your two rows would look like this: Row 1 ===== *john_doe (I prefer natural keys to surrogate) *north *1 Y 2007-03-01 2007-10-01 blah blah Row 2 ===== *john_doe *south *1 Y 2007-10-01 2007-03-01 blah blah If you want to keep a history of past addresses, just add a new row with an obs_no of 2 and set the active_ind to 'N' for the old row. All your queries will have to contain a where clause (active_ind = 'Y') to keep the old rows from showing up. I hope this helps. Tony On 10/24/07 7:30 AM, "Bastien Koert" <bastien_k@hotmail.com> wrote: > > I would approach this by having a main people table (with a unique id of > course) and then create a second addresses table which uses the people Id key > as the foreign key to this table...then you can have multiple (more than two) > addresses for those users, you could add a season in the addresses to be able > to pull the correct one based on date