[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. So... 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 [EMAIL PROTECTED]
Re: [PHP-DB] 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 [EMAIL PROTECTED] 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. So... 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 [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Question about database design
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: [EMAIL PROTECTED] 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. So... 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 [EMAIL PROTECTED] _ Express yourself with free Messenger emoticons. Get them today! http://www.freemessengeremoticons.ca/?icid=EMENCA122
Re: [PHP-DB] PHP and MySQL design question
It shouldn't matter what order the columns are referenced. Mysql is smart enough to optimize the query based on the available indexes. In fact, it should be good enough just to create an index on each column that will be searched -- not on combinations of columns. Do you have any performance numbers to believe that this is not the case? Roberto Byte Smokers wrote: Hello I did look into the info from EXPLAIN. I can create the indexes also but then I have to create indexes with all permutation of column order if I want to get good performance from all search query regardless of what order user enters the column. On 10/23/07, Theodoros Goltsios [EMAIL PROTECTED] wrote: I guess EXPLAIN will do the job for you. First of all in order to ensure what is the index used by your queries and then how to improve performance by making the right indexes. Theodoros Goltsios Kinetix Tele.com Support Center email: [EMAIL PROTECTED], [EMAIL PROTECTED] Tel. Fax: +30 2310556134 WWW: http://www.kinetix.gr/ O/H Byte Smokers ??: Hello all I have a table like: CREATE TABLE `benchmarks` ( `name` varchar(50) NOT NULL default '', `logic` varchar(50) NOT NULL default '', `status` varchar(50) NOT NULL default '', `difficulty` int(11) NOT NULL default '0', `xmldata` longblob, PRIMARY KEY (`name`), KEY `logic` (`logic`), KEY `status` (`status`), KEY `difficulty` (`difficulty`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a search form like: http://craig.cs.uiowa.edu/smt/index.php where each field corresponds to each field in the table. Now user can select any column arbitrality and I generate the select statement depending upon that by looping through each listbox. As you can see that the user can select the columns in any arbitrary order and a query like: select name from benchmarks where logic = AUFLIA and status = sat returns result after sometime. I added another index like (logic, status) and the query returns result in blazing speed but then a query like: select name from benchmarks where status = sat and logic = AUFLIA takes more time to return the result as index were not created in that order. I can get all the possible combination by having indexes like: abc bc c ac (where a,b,c are columns) but it dosnt scale well. If later on I decide to add another column, I have to add all permutation in the indexes too. How can I solve this problem? Thank you. Ritesh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 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 [EMAIL PROTECTED] 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. So... 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 [EMAIL PROTECTED] -- 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 [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about database design
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: [EMAIL PROTECTED] 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. So... 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
Re: [PHP-DB] 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 [EMAIL PROTECTED] 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: [PHP-DB] Question about database design
Nope, you got it Bastien CC: php-db@lists.php.net From: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Question about database design Date: Wed, 24 Oct 2007 15:18:05 -0400 To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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. So... 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] _ 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
