PHP DB - RE: [PHP-DB] Question about database design

Author

RE: [PHP-DB] Question about database design

Bastien Koert

2007-10-24, 7:00 pm

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

bastien> To: php-db@lists.php.net> From: japruim@raoset.com> Date: Wed, 24 Oct 2007 09:06:29 -0400> Subject: [PHP-DB] Question about database design> > ********I sent this to the MySQL list but didn't receive any > response, My apologies if you have already received this.> > > Hi Everyone,> > So having learned my lesson with the last application, I am trying to> plan out the addition of a feature to my database application.> Basically, some of mycustomers go south for the winter ("Snow> Birds") what I would like to do is have away of storing both their> addresses in the database, and have it so that the people> administering the list can choose between wether they are up north or> down south without having to erase the old address.> > For that I was thinking creating a second table "SnowBirds" and list> their southern addresses in there and then when the list admin clicks> on the edit button for their name, it would also be able to pull up a> list of the the addresses stored and associated with that person.> > I'm also considering adding a date range for the addresses so that if> they know they'll be south from November to March it will check the> date and switch between the record accordingly BEFORE exporting to> excel.> > Now... I haven't really asked a question yet but gave some background> into what I want to do. Sooooo... Here's the question, does anyone> have any advice on the best way to do it?Am I right in thinking that> a second table is required? Would it be called a Relational database?> Or have I missed the terminology?> > Any help would be greatly appreciated!> > Thanks for looking!> > ohhh... and in case itmakes a difference it's MySQL 5.* and I'll be> writing the stuff to accessthat database with php 5.> > --> > Jason Pruim> Raoset Inc.> Technology Manager> MQC Specialist> 3251 132nd ave> Holland, MI, 49424> www.raoset.com> japruim@raoset.com> >
________________________________________
_________________________
Express yourself with free Messenger emoticons. Get them today!
http://www.freemessengeremoticons.ca/?icid=EMENCA122

Jason Pruim

2007-10-24, 7:00 pm

I think I understand what you are saying here... On the main table
just list the persons name and then on a second table use a 1 to many
relationship on a foreign key to link all the addresses into the
name? Or did I miss the mark? :)

On Oct 24, 2007, at 9:30 AM, Bastien Koert 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
>
> bastien
>
> trying to
> north or
> clicks
> up a
> that if
> background
> that
> database?
>
>
> Express yourself with free Messenger emoticons. Get them today!

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com

Tony Grimes

2007-10-24, 7:00 pm

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

Sponsored Links