Results 1 to 3 of 3

Thread: Help with database design?

  1. #1
    Join Date
    Apr 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Help with database design?

    Hello,

    I've worked with MySQL databases before, but have never designed one!

    I wish to design a simple order system, where patrons can order two items:

    1. A t-shirt that comes in 5 different sizes (XS,S,M,L,XL), and ten different colors (C1,C2, . . . C10);

    2. A mug that comes in only 1 size, but has 2 different colors.

    I want an order to be able to include as many t-shirts and mugs as a person would like to order. I understand the basics of relational databases, with foreign keys, etc., but really don't know how to structure the database.

    Can anyone help?

    Cheers,

    Alec

  2. #2
    Join Date
    Mar 2009
    Location
    NJ, USA
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Default

    When the user selects the shirt and color it would be from a form yes? assuming this is correct then you can have the following rows and build your html form to fit them.

    ssize -CHAR 2
    scolor -CHAR 2
    quant - INT
    fname -VARCHAR 15
    lname -VARCHAR 25
    address - VARCHAR 30
    state - CHAR 2
    zip - INT 8

    Then have PHP email the distributer the above information in the same submit script.

    The multiple items is a bit harder. I'm thinking along the lines of multiple small forms which appear one after the other and share a submit button which adds the items to the database one at a time as different orders.

    Perhaps having a database for temporary storage? I hope this helps, but hopefully a db guru comes to your aid.

  3. #3
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    Quote Originally Posted by alecwild View Post
    I wish to design a simple order system, where patrons can order two items:

    I want an order to be able to include as many t-shirts and mugs as a person would like to order.
    Would you like some type of user system to track and/or hold "user" information? EG, would you like to a way for a returning user to place a second/third/... order and have their details remembered from previous times, for promotional or discount purposes?

    Quote Originally Posted by adrielgreene
    When the user selects the shirt and color it would be from a form yes? assuming this is correct then you can have the following rows and build your html form to fit them.
    Quote Originally Posted by alexcwild
    I understand the basics of relational databases, with foreign keys, etc., but really don't know how to structure the database.
    A true ordering system would allow for any number of items in the system, however if you are only going to have 2 products, alec you shouldn't need to worry about multiple tables for just the product.

    My suggestion would be to have both a login system and a product database. This would require more tables (1 for user, 1 for products, 1 for order), however Alec, it would allow you to potentially add any number of products.

    User table
    Code:
    CREATE TABLE `User` (
    `uid` INT(16) NOT NULL UNSIGNED AUTO-INCREMENT DEFAULT '0',
    `fname` VARCHAR(64),
    `lname` VARCHAR(64),
    `baddress` VARCHAR(255),
    `bcity` VARCHAR(64),
    `bstate` VARCHAR(8),
    `bpost_code` VARCHAR(8),
    `bcountry` VARCHAR(8),
    `saddress` VARCHAR(255),
    `scity` VARCHAR(64),
    `sstate` VARCHAR(8),
    `spost_code` VARCHAR(8),
    `scountry` VARCHAR(8),
    PRIMARY KEY(`uid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Note: b = billing; s = shipping;
    Some people have a different billing and shipping address (eg PO Box users in USA)


    Product table
    Code:
    CREATE TABLE `Products` (
    `pid` INT(16) NOT NULL UNSIGNED AUTO-INCREMENT DEFAULT '0',
    `title` VARCHAR(64) NOT NULL DEFAULT '',
    `desc` VARCHAR(128),
    `long_desc` TEXT,
    `size` TEXT,
    `color` TEXT,
    PRIMARY KEY(`pid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Order Table
    Code:
    CREATE TABLE `Order` (
    `oid` INT(16) NOT NULL UNSIGNED AUTO-INCREMENT DEFAULT '0',
    `pid` INT(16) NOT NULL DEFAULT '0',
    `uid` INT(16) NOT NULL DEFAULT '0',
    `qty` FLOAT(8) DEFAULT '0',
    `size` VARCHAR(8) DEFAULT 'M',
    `color` VARCHAR(16)
    PRIMARY KEY(`oid`,`pid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    To display you would run a SELECT query such as

    Code:
    SELECT `pid`,`title`,`desc`, `long_desc`, `size`,`color` FROM `Products`
    When you put in the shirt, you would use some type of delineated system, so a possible result may be

    Code:
    '1', 'X Company Shirt', 'This shirt is etcetc', 'more detailed description', 'XS;S;M;L;XL', 'Red;Blue;Black;Gray;etcetc'
    '2', 'X Company Mug', 'desc', 'long desc', 'M', 'White;Black'
    Then in your results, you can use your server-side language to separate and show the fields that have multiple values (PHP would use the explode() and implode() methods)

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
  •