SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advice on how best to store data

    I am storing data about removal companies in a database and am not sure how best to store it.

    Basically I have all the companies details such as name, address, telephone, email etc

    I currently store this in a table called "members"

    Now I also need to store the cost of removals for various volumes and distances

    i.e

    Distance | Volume 1 | Volume 2
    ------------------------------
    25 | 25 | 40
    ------------------------------
    50 | 40 | 60
    ------------------------------
    100 | 70 | 90
    ------------------------------
    150 | 90 | 120
    ------------------------------

    Hopefully the formatting hasn't messed up and you get the general idea.

    I was going to just store these costs in the members table as follows:

    cost25_volume1
    cost25_volume2
    cost50_volume1
    cost50_volume2
    cost100_volume1
    cost100_volume2
    etc

    However I am thinking perhaps this is not the most sensible way to store the data. Unfortunately I am not sure what the alternative is and therefore seek advice.

    Thanks

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    create another table with the following columns:

    memberid
    distance
    volume (1 or 2)
    cost

    of these, the first three should be the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 - I should hire you as my tutor

    Do you mind explaining why this data is best kept in a separate table?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    because if you store it in the member table the way you mentioned in the first post, the queries to do anything at all will all be monstrously complicated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah thanks Rudy, I guess this will become apparent when I start trying to work with the data.

    Cheers!

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  6. #6
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have done what Rudy suggested and created a separate table for prices.

    Now I have an admin page that allows a member to amend all their prices and in order to update the database I am having to make multiple "INSERT" and "UPDATE" calls in a loop. I am concerned this is not good practice though it does seem to work.

    Here is my code. I would appreciate any advice as to whether what I am doing codewise here is OK?

    Code:
    
    		if ($_POST["submit"]) { 
    
    				$distances = array("25", "75", "150", "200", "250", "300", "350", "400", "450", "500", "550", "600");
    				$volumes = array("300", "700", "1000");
    
    				$howManyDistances = count($distances);
    				$howManyVolumes = count($volumes);
    				
    
    				for ($counter = 0; $counter <= $howManyDistances; $counter++) {
    
    					for ($counter2 = 0; $counter2 <= $howManyVolumes; $counter2++) {
    
    						$distance = $distances[$counter];
    						$volume = $volumes[$counter2];						
    
    						echo "<br />";
    
    						$fieldValue = strip_tags($_POST[('cost' . $distance . '_' . $volume)]);
    						
    
    						$query="select * FROM prices WHERE memberId = '$id' AND distance = '$distance' AND volume='$volume'";
    						$result = @mysql_query ($query); // Run the query
    
    						$row=mysql_fetch_array ($result, MYSQL_NUM); // Return a record if applicable
    
    
    						if ($row) {
    
    							// A record was pulled from database therefore we need to update rather than insert
    
    							$query = "UPDATE prices SET cost='$fieldValue'
    							WHERE memberId = '$id' AND distance = '$distance'AND volume='$volume' ";
    
    
    						} else {
    
    
    							$query = "INSERT INTO prices (memberId,
    										      distance,
    										      volume,
    										      cost)
    							VALUES ('$id',
    							'$distance',
    							'$volume',
    							'$fieldValue')";
    
    						}						
    	
    						mysql_query($query) or die(mysql_error());				
    
    						echo "<br />";
    
    					}
    	
    				}
    
    				
    
    				
    
    				echo "Prices successfully updated";
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you can probably do it with one statement and no loops: http://dev.mysql.com/doc/refman/5.1/...duplicate.html
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks longneck - though looking at that link, I am not sure if my table has any unique keys for that to work. I am a bit of a newbie so not sure
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •