Question about database design

--Apple-Mail-10-175624899
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

********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 my customers 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 it makes a difference it's MySQL 5.* and I'll be
writing the stuff to access that database with php 5.

--

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



--Apple-Mail-10-175624899--

Re: Question about database design

Hi Jason,

There are a couple ways you could do this. Yes, this would make it a
relational database.

If you go with a second table, you'll want to be sure to include the UID
from the main address table in the "SnowBirds" table so that they are
linked. The UID in the second table would actually be a foreign key
that links it to the main address table. It could also act as the
primary key of the "SnowBirds" table since it will be unique. The other
importance for this relationship of the UID is that if you delete a
person and their address from the main table, you'll probably want to
delete their seasonal address, too.

You may also want to have a binary column in your main address table to
indicate to your PHP script whether or not to look for another address
in the SnowBirds tables. I would do this so that you aren't wasting an
SQL query if it isn't needed. May not be a big deal if you have limited
tables, but as list of queries increase, there is no need for extra queries.

You could add a start date and end date column the "SnowBirds" database.
Sounds like for your purposes that is a good idea.

If you I missed anything or you have more specific questions, feel free
to email me on or off list.

Cheers,
Tim

Tim McGeary '99, '06G
Senior Systems Specialist
Lehigh University
610-758-4998
tim.mcgeary@lehigh.edu


Jason Pruim wrote:
> ********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 my customers 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 it makes a difference it's MySQL 5.* and I'll be
> writing the stuff to access that database with php 5.
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> japruim@raoset.com
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Question about database design

--_a0603435-53c7-4c05-899a-0112fe974d83_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


I would approach this by having a main people table (with a unique id of co=
urse) 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 t=
wo) addresses for those users, you could add a season in the addresses to b=
e able to pull the correct one based on date
=20
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 hav=
ing learned my lesson with the last application, I am trying to> plan out t=
he addition of a feature to my database application.> Basically, some of my=
customers 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 a=
re 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 sou=
thern addresses in there and then when the list admin clicks> on the edit b=
utton for their name, it would also be able to pull up a> list of the the a=
ddresses stored and associated with that person.> > I'm also considering ad=
ding 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 recor=
d 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 calle=
d a Relational database?> Or have I missed the terminology?> > Any help wou=
ld be greatly appreciated!> > Thanks for looking!> > ohhh... and in case it=
makes a difference it's MySQL 5.* and I'll be> writing the stuff to access=
that database with php 5.> > --> > Jason Pruim> Raoset Inc.> Technology Ma=
nager> MQC Specialist> 3251 132nd ave> Holland, MI, 49424> www.raoset.com> =
japruim@raoset.com> >=20
____________________________________________________________ _____
Express yourself with free Messenger emoticons. Get them today!
http://www.freemessengeremoticons.ca/?icid=3DEMENCA122=

--_a0603435-53c7-4c05-899a-0112fe974d83_--

Re: Question about database design

Hi Tim,

Right now the customer I have has about 1,000 records which I know
isn't alot for MySQL to handle, but if people like the application we
could end up with 15 to 20 all having around 1,000 or more records
which would add up more on the database. Although right now I plan to
have a database per customer, easier to keep the info separate. I'm
just beginning with MySQL and PHP (Although I know and understand PHP
more then MySQL)

So just to double check I understand what you are saying, you would
set up a database with 2 tables, on the first one would be something
like name, address, city, state, zip, Primary Key. And then on the
second table it would have Name, address, city, state, zip, foreign
key? and the foreign key someone links the 2 records?

Like I said, I'm still learning MySQL :) Know of any good articles
that describe it for a beginner?


On Oct 24, 2007, at 9:28 AM, Tim McGeary wrote:

> Hi Jason,
>
> There are a couple ways you could do this. Yes, this would make it
> a relational database.
>
> If you go with a second table, you'll want to be sure to include
> the UID from the main address table in the "SnowBirds" table so
> that they are linked. The UID in the second table would actually
> be a foreign key that links it to the main address table. It could
> also act as the primary key of the "SnowBirds" table since it will
> be unique. The other importance for this relationship of the UID
> is that if you delete a person and their address from the main
> table, you'll probably want to delete their seasonal address, too.
>
> You may also want to have a binary column in your main address
> table to indicate to your PHP script whether or not to look for
> another address in the SnowBirds tables. I would do this so that
> you aren't wasting an SQL query if it isn't needed. May not be a
> big deal if you have limited tables, but as list of queries
> increase, there is no need for extra queries.
>
> You could add a start date and end date column the "SnowBirds"
> database. Sounds like for your purposes that is a good idea.
>
> If you I missed anything or you have more specific questions, feel
> free to email me on or off list.
>
> Cheers,
> Tim
>
> Tim McGeary '99, '06G
> Senior Systems Specialist
> Lehigh University
> 610-758-4998
> tim.mcgeary@lehigh.edu
>
>
> Jason Pruim wrote:
>> ********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 my customers 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 it makes a difference it's MySQL 5.* and I'll be
>> writing the stuff to access that database with php 5.
>> --
>> Jason Pruim
>> Raoset Inc.
>> Technology Manager
>> MQC Specialist
>> 3251 132nd ave
>> Holland, MI, 49424
>> www.raoset.com
>> japruim@raoset.com
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Question about database design

--Apple-Mail-16-197921082
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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
>
> > 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 my customers 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 it makes a difference it's MySQL 5.* and I'll be
> > writing the stuff to access that 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!

--

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



--Apple-Mail-16-197921082--

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Question about database design

Nope, you got it


Bastien

________________________________> CC: php-db@lists.php.net> From: japruim@r=
aoset.com> Subject: Re: [PHP-DB] Question about database design> Date: Wed,=
24 Oct 2007 15:18:05 -0400> To: bastien_k@hotmail.com>> I think I understa=
nd 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 t=
o 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 tab=
le...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.co=
m>> Date: Wed, 24 Oct 2007 09:06:29 -0400>> Subject: [PHP-DB] Question abou=
t database design>>>> ********I sent this to the MySQL list but didn't rece=
ive any>> response, My apologies if you have already received this.>>>>>> H=
i 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 my customers 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 c=
hoose between wether they are up north or>> down south without having to er=
ase the old address.>>>> For that I was thinking creating a second table "S=
nowBirds" and list>> their southern addresses in there and then when the li=
st 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 pe=
rson.>>>> I'm also considering adding a date range for the addresses so tha=
t 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>> exce=
l.>>>> Now... I haven't really asked a question yet but gave some backgroun=
d>> into what I want to do. Sooooo... Here's the question, does anyone>> ha=
ve any advice on the best way to do it? Am I right in thinking that>> a sec=
ond table is required? Would it be called a Relational database?>> Or have =
I missed the terminology?>>>> Any help would be greatly appreciated!>>>> Th=
anks for looking!>>>> ohhh... and in case it makes a difference it's MySQL =
5.* and I'll be>> writing the stuff to access that database with php 5.>>>>=
-->>>> Jason Pruim>> Raoset Inc.>> Technology Manager>> MQC Specialist>> 3=
251 132nd ave>> Holland, MI, 49424>> www.raoset.com>> japruim@raoset.com>>>=
>> ________________________________> Express yourself with free Messenger e=
moticons. Get them today!> --> Jason Pruim> Raoset Inc.> Technology Manager=
> MQC Specialist> 3251 132nd ave> Holland, MI, 49424> www.raoset.com> japru=
im@raoset.com

____________________________________________________________ _____
R U Ready for Windows Live Messenger Beta 8.5? Try it today!
http://entertainment.sympatico.msn.ca/WindowsLiveMessenger=

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php