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

Author

Re: [PHP-DB] Question about database design

Tim McGeary

2007-10-24, 7:00 pm

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

Jason Pruim

2007-10-24, 7:00 pm

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

Sponsored Links