Results 1 to 2 of 2

Thread: Anyone have a simple database schema template available?

  1. #1
    Join Date
    Feb 2003
    Posts
    11

    Anyone have a simple database schema template available?

    I'm trying to create a product catalog for a website and, although I'm getting some things to work, I'm beginning to realize that perhaps I'm going the wrong way with the whole database setup.

    Currently I have a vendors table (primary=vend_id), a types table (primary=types_id), and a products table which includes both vend_id and types_id as foreign keys and I have a primary called simply ID.
    I also have a column called prodDesc which is a column that will hold a bunch of point-form descriptions of the items.

    Firstly, what's the best schema for this thing? Am I on the right track? Secondly, how do I set up the point-form of the prodDesc column so that when I display a product the prodDesc column prints in a point-form list?

    I hope I've made it clear...thanks for any help you may have

  2. #2
    Join Date
    Mar 2003
    Location
    Jacksonville, Florida
    Posts
    52

    Sounds good...

    This track sounds good...

    Three ways to do it are...

    Possibility A:
    When inserting the data in to the prodDesc, seperate each line with a delimeter (a character or set of characters that is used to seperate simple data sets). I would choose the newline character '\n'.
    Then when you get the text out perform this, replacing "\n" with the proper character that you decide will separate each line:


    To insert into database:

    $description = "";
    foreach ( $descriptions as $desc_line )
    $description .= $desc_line . '\n';

    $insert_statement = 'INSERT INTO db ( prodDesc, prodID ) VALUES ( ' . addslashes($description) . ', ' . $prodID . ' )';
    mysql_query( $insert_statement );


    To query database:

    $result = mysql_query( $your_query );
    $array = mysql_fetch_assoc( $result );
    $desc_from_database = stripslashes($array['prodDesc']);
    $descriptions = explode( "\n", $desc_from_database );

    echo '<UL>';
    foreach ( $descriptions as $desc_line )
    echo "<LI>$desc_line</LI>";
    echo '</UL>';


    Possibility B:
    Include HTML formatting in before putting it in the database.

    To insert into database:

    $description = "<UL>";
    foreach ( $descriptions as $desc_line )
    $description .= '<LI>' . $desc_line . '</IL>';
    $description .= '</UL>';

    $insert_statement = 'INSERT INTO db ( prodDesc, prodID ) VALUES ( ' . addslashes($description) . ', ' . $prodID . ' )';
    mysql_query( $insert_statement );


    To query database:

    $result = mysql_query( $your_query );
    $array = mysql_fetch_assoc( $result );
    $desc_from_database = stripslashes($array['prodDesc']);
    echo $desc_from_database;

    Possibility C:
    A little more work, but may be worth it to save room and may be a little easier programmatically. This involves simply creating yet another table to hold multiple descriptions for each prod ID. It would have two columns, prodDesc and prod ID as Foreign Key in products table. For example:

    To insert into database:

    foreach ( $descriptions as $desc_line ){
    $insert_statement = 'INSERT INTO desc_table ( prodDesc, prodID ) VALUES '. addslashes($description) . ', ' . $current_prod_id . ' )';
    mysql_query( $insert_statement );
    }

    To query database:

    $query = "SELECT prodDesc AS desc FROM desc_table WHERE prodID = $current_prod_id";
    $result = mysql_query( $your_query );
    echo '<UL>';
    while ( $array = mysql_fetch_assoc( $result ) )
    {
    $desc_from_database = stripslashes($array['prodDesc']);
    echo "<LI>$desc_from_database</LI>";
    }

    Depending on your needs, a different scenario may apply. If you have limited database space, or description lengths that vary wildly, you might consider option C. Option A is great if the descriptions are about the same length. Option B is better on processing time, however...if the database is not located on the same server, and a slow link is present these are extra characters that must be transmitted. This is also a consideration with Option C, considering INSERTion may take longer, especially over a slower link. Option A is a good choice too.

    Let us know what you choose or if you have any questions...

Posting Permissions

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