'lo folks, new to the board; I didn't see a topic discussing, specifically, database design, so I figured I'd just post a thread in the General Development issues section -- though I reckon you'll need a DB section exclusively, with topics for MySQL, FirebirdSQL, PostgreSQL, etc.

Anyhow, since database-driven websites are the thing these days, I'm developing an online application and ecommerce market for a retail clothing store, using a decent db design I've been working on for a few days, and I've been through a couple of the normal forms, but I haven't done any optimization. Here's the creation statements, take a look and give some good feedback, eh? :)

CREATE TABLE billing_address (
billing_addressID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
custID INTEGER UNSIGNED NOT NULL,
address VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL,
state VARCHAR(2) NOT NULL,
zip NUMERIC(6) NOT NULL,
PRIMARY KEY(billing_addressID),
INDEX billing_address_FKIndex1(custID)
)
TYPE=InnoDB;

CREATE TABLE categories (
catID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(255) NOT NULL,
cat_parent INTEGER UNSIGNED NOT NULL,
cat_image TEXT NULL,
PRIMARY KEY(catID)
)
TYPE=InnoDB;

CREATE TABLE colors (
colorID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
color VARCHAR(20) NOT NULL,
PRIMARY KEY(colorID)
)
TYPE=InnoDB;

CREATE TABLE customers (
custID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
cust_incomeset INTEGER UNSIGNED NOT NULL,
cust_ageset INTEGER UNSIGNED NOT NULL,
cust_fname VARCHAR(55) NOT NULL,
cust_lname VARCHAR(55) NOT NULL,
cust_sex ENUM('Male','Female') NOT NULL DEFAULT 'Male',
cust_number NUMERIC NOT NULL,
date_added TIMESTAMP NOT NULL,
PRIMARY KEY(custID),
INDEX customers_FKIndex1(cust_ageset),
INDEX customers_FKIndex2(cust_incomeset)
)
TYPE=InnoDB;

CREATE TABLE customer_ageset (
ageID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
ageset_label VARCHAR(60) NOT NULL,
PRIMARY KEY(ageID)
);

CREATE TABLE customer_incomeset (
incomeID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
incomeset_label VARCHAR(60) NOT NULL,
PRIMARY KEY(incomeID)
)
TYPE=InnoDB;

CREATE TABLE inventory (
sizeID INTEGER UNSIGNED NOT NULL,
colorID INTEGER UNSIGNED NOT NULL,
prodID INTEGER UNSIGNED NOT NULL,
qty_onhand INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(sizeID, colorID, prodID),
INDEX inventory_FKIndex1(prodID, colorID, sizeID)
)
TYPE=InnoDB;

CREATE TABLE invoices (
invoice_num INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
soldto_custID INTEGER UNSIGNED NOT NULL,
sale_total DOUBLE NOT NULL,
time_stamp TIMESTAMP(14) NOT NULL,
sale_location ENUM('DALLAS','ONLINE') NOT NULL DEFAULT 'DALLAS',
payment_method ENUM('CHARGE','CASH','CHECK') NOT NULL DEFAULT 'CASH',
PRIMARY KEY(invoice_num, soldto_custID),
INDEX invoices_FKIndex1(soldto_custID)
)
TYPE=InnoDB;

CREATE TABLE products (
prodID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
prod_name VARCHAR(255) NOT NULL,
prod_styleNum VARCHAR(20) NOT NULL DEFAULT 'GENMERCH00',
prod_desc TEXT NULL,
prod_margin INTEGER UNSIGNED NOT NULL DEFAULT 50,
prod_costprice DOUBLE PRECISION NOT NULL,
PRIMARY KEY(prodID)
)
TYPE=InnoDB;

CREATE TABLE products_has_categories (
prodID INTEGER UNSIGNED NOT NULL,
catID INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(prodID, catID),
INDEX products_has_categories_FKIndex1(prodID),
INDEX products_has_categories_FKIndex2(catID)
)
TYPE=InnoDB;

CREATE TABLE product_options (
prodID INTEGER UNSIGNED NOT NULL,
colorID INTEGER UNSIGNED NOT NULL,
sizeID INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(prodID, colorID, sizeID),
INDEX product_options_FKIndex1(prodID),
INDEX product_options_FKIndex2(sizeID),
INDEX product_options_FKIndex3(colorID)
)
TYPE=InnoDB;

CREATE TABLE saleTransactions (
invoice_num INTEGER UNSIGNED NOT NULL,
inventory_prodID INTEGER UNSIGNED NOT NULL,
inventory_colorID INTEGER UNSIGNED NOT NULL,
inventory_sizeID INTEGER UNSIGNED NOT NULL,
soldto_custID INTEGER UNSIGNED NOT NULL,
item_rate DOUBLE NOT NULL,
total_amount DOUBLE NOT NULL,
qty_ordered INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(invoice_num, inventory_prodID, inventory_colorID, inventory_sizeID, soldto_custID),
INDEX saleTransactions_FKIndex1(invoice_num, soldto_custID),
INDEX saleTransactions_FKIndex2(inventory_sizeID, inventory_colorID, inventory_prodID)
)
TYPE=InnoDB;

CREATE TABLE shipping_address (
shipping_addressID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
custID INTEGER UNSIGNED NOT NULL,
address VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL,
state VARCHAR(2) NOT NULL,
zip NUMERIC(6) NOT NULL,
PRIMARY KEY(shipping_addressID),
INDEX shipping_address_FKIndex1(custID)
)
TYPE=InnoDB;

CREATE TABLE sizes (
sizeID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
size VARCHAR(20) NULL,
PRIMARY KEY(sizeID)
)
TYPE=InnoDB;

It's still a little rough, because I have to readress the information gathering process -- new business documents that are used in day-to-day transactions. Still, this is good for Customer tracking, invoicing, and inventory management so far.

Perhaps merge the billing and shipping address table and put a flag column to distinguish between the two.

Other than that looks fine but maybe someone else has more feedback.


:)