Help with database design?

[alecwild]

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

[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.

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.

[boogyman]

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?

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.

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

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

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

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

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

'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)