Page 1 of 1

CMS Logic

Posted: Fri Mar 10, 2006 11:40 pm
by Jim_Bo
Hi,

Im trying to come up with the best way to store data in a databse for products. I will be using a CMS system to add/edit/delete the records.

The products will contain the the following fields

| id | name | description | PB | price |

Now the issue is that the PB & price fields could contain upto 3 records per item ie:

PB = PB6, PB9, PB14

Price = 12.00, 14.00, 19.00

so when called out using a loop I want it to display the name, description then list the PB's & prices one under another for that record, then move onto the next record etc etc

I was thinking maybe storing them each within the corosponding field using something like "," to seperate them, then upon calling the records out explode those 2 fields using "explode(',',...."

But im thinking that when loop through the items when calling them back this will mess up the structure?

What would be a pluasble way to go about this situation?


Thanks

Posted: Fri Mar 10, 2006 11:55 pm
by feyd
Two options
  1. Separate table for PB (price break?) and price
    Pros
    More flexible; allows for any number of price breaks

    Cons
    Slightly more complex query structuring
    • Separate fields.
      Pros
      More simple; queries and coding can, overall, be more simple

      Cons
      Limiting; Finite number of price breaks
      Requires "special case" coding to know which field to update in the table.

    Posted: Sat Mar 11, 2006 2:36 am
    by Jim_Bo
    Hmmz,

    I guess 3 seperate tables + joins it is .. altho 3 tables seems extreme for such a small project.

    Does that mean that using a seperator and explode wont do the job at all?


    Thanks

    Posted: Sat Mar 11, 2006 3:50 am
    by NightFox
    I wouldn't create extra tables, I'd just use the explode() and implode() functions. And you could have unlimited amounts.
    For example:

    Code: Select all

    $prices[] = "15.99"; (sets $price[0] = 15.99)
    $prices[] = "12.99"; (sets $price[1] = 12.99)
    $prices[] = "9.99"; (sets $price[2] = 9.99)
    $insert_prices = implode("|",$prices);  //$insert_prices now = "15.99|12.99|9.99"
    
    $pb[] = 14;
    $pb[] = 1;
    $pb[] = 401;
    $insert_pb = implode("|",$pb);  //$insert_pb now = "14|1|401"
    $query = dbquery("INSERT INTO XXTABLEXX blah blah blah"); //(using PHP-Fusion defined db query function)
    Now, to get the data, query the database and we'll say the query places the prices into $qprices and the PB into $qpb:

    Code: Select all

    $prices = explode("|",$qprices);
    $s_pb = explode("|",$qbp);
    foreach ($s_pb as $key => $value) {
         $pb[$key] = "PB".$value;  //automatically adds the PB prefix to the numbers to keep your database smaller (every bit helps;) )
    }
    So now you have all your data for each of those two fields in a simple variable.

    Posted: Sat Mar 11, 2006 5:10 am
    by Jim_Bo
    Hi,

    Yer thats what I was thinking, Just wasnt sure if using explode within the query loop would keep the structure of each item_id when echoing them out of the db .. ?

    cheers

    Posted: Sat Mar 11, 2006 5:51 am
    by s.dot
    explode won't mess with any database value, it simply breaks the string up into pieces

    Code: Select all

    $result = mysql_query("SELECT `str` FROM `table` LIMIT 10");
    
    echo mysql_result($result,0);  // should pring something like a|b|c|d|e that it pulled from the database
    
    while($array = mysql_fetch_assoc($result)){
       $pieces = explode("|",$array['str']);
       echo $pieces[0]; // a
       echo $pieces[1]; // b
       echo $pieces[2]; // c
    }

    Posted: Sat Mar 11, 2006 8:53 am
    by feyd
    I suggested two tables, not three.

    Posted: Sat Mar 11, 2006 2:10 pm
    by Jim_Bo
    Hi,

    Im not sure how 2 tables will work as 2 fields will have up to 3 values ..

    PB and price

    So you say hold both values in the second table?


    Thanks

    Posted: Sat Mar 11, 2006 2:18 pm
    by feyd
    Table A
    ID, name, description

    Table B
    ID, Table A ID, Price Break, Price

    Posted: Sat Mar 11, 2006 6:21 pm
    by NightFox
    I still believe explode/implode is the way to go :)

    Posted: Sat Mar 11, 2006 7:20 pm
    by Jim_Bo
    Hi,

    the way i see it, feyds way will still need to be imploded and exploded to acheive the job as well?


    Thanks

    Posted: Sat Mar 11, 2006 7:27 pm
    by feyd
    Not at all. Each price break-price combo has their own record in Table B. Table B references the Table A record they apply to. This allows any number of price breaks, be it one or 500. It's a one-to-many relationship between Table A and Table B.

    Posted: Sun Mar 12, 2006 1:23 am
    by NightFox
    ya... my philosophy is, the less bulk in your db, the better. That's why I'd prefer ex/implode functions.
    And it will also be useful when editing the db manually because all the data will be in one row, not two tables with multiple rows.

    Posted: Sun Mar 12, 2006 9:08 am
    by John Cartwright
    Databases are designed to handle large amount of data, somestimes millions of rows of data so a properly designed database is much more portable, logical, and is likely faster than having a pull your data (unformatted) and do the formatting yourself..

    Posted: Sun Mar 12, 2006 9:13 am
    by noginn
    3 separate tables seems to be the logical choice. Would probably be faster.