+ Reply to Thread
Results 1 to 2 of 2

Thread: DB Design: Normalization/Optimization

  1. #1
    Account Disabled ahad is on a distinguished road
    Join Date
    Jun 2004
    Location
    Arlington, TX
    Posts
    1

    Lightbulb DB Design: Normalization/Optimization

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

    Code:
    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.

  2. #2
    Full Member cdingman is on a distinguished road
    Join Date
    Jun 2004
    Location
    South Amboy NJ
    Posts
    96
    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.


    Chris Dingman
    Webmaster
    <A HREF="http://www.cabarettheatre.org" target="_blank">Cabaret Theatre</A><BR>
    <A HREF="http://www.twinlightdesigns.com" target="_blank">Twinlight Designs</A>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Posting Permissions

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