#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Relational Database Design


    Hello;

    I want to build a flawless model so I can apply it to the larger systems.

    Is this designed properly? Did I miss anything important? Can I use the same model and use it for very large databases?
    Code:
    CREATE TABLE IF NOT EXISTS `customers`
      (
         `id`   INT(11) NOT NULL auto_increment,
         `name` VARCHAR(45) DEFAULT NULL,
         PRIMARY KEY (`id`)
      )
    engine=innodb
    DEFAULT charset=utf8
    auto_increment=1;
    Code:
    CREATE TABLE IF NOT EXISTS `products`
      (
         `id`    INT(11) NOT NULL auto_increment,
         `name`  VARCHAR(45) NOT NULL,
         `price` INT(11) NOT NULL,
         PRIMARY KEY (`id`),
         UNIQUE KEY `name_unique` (`name`)
      )
    engine=innodb
    DEFAULT charset=utf8
    auto_increment=1;
    Code:
    CREATE TABLE IF NOT EXISTS `shopping_cart`
      (
         `customer_id` INT(11) NOT NULL,
         `product_id`  INT(11) NOT NULL,
         `quantity`    INT(11) NOT NULL,
         UNIQUE KEY `customer_product` (`customer_id`, `product_id`),
         KEY `fk_customer_id_idx` (`customer_id`),
         KEY `fk_product_id_idx` (`product_id`)
      )
    engine=innodb
    DEFAULT charset=utf8;
    Code:
    ALTER TABLE `shopping_cart`
      ADD CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES
      `customers` (`id`) ON DELETE no action ON UPDATE no action,
      ADD CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES
      `products` (`id`);
    Also why can't I create UNIQUE constraint on multiple columns in MySQL Workbench EER diagram?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Is this designed properly?
    no

    there is no way to distinguish a "cart" (a collection of items purchased together)

    this means you can't tell which items were purchased together one time, from those items purchased another time

    also, the design prevents a customer from ever purchasing another one of any item
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    no

    there is no way to distinguish a "cart" (a collection of items purchased together)

    this means you can't tell which items were purchased together one time, from those items purchased another time

    also, the design prevents a customer from ever purchasing another one of any item
    How did I miss that?!!!
    Code:
    ALTER TABLE `shopping_cart` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
    Is it now a reliable system?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Is it now a reliable system?
    no

    adding a surrogate key to a flawed design does not improve the design

    you need a new table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    no

    adding a surrogate key to a flawed design does not improve the design

    you need a new table
    I see. Still flawed?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by zxcvbnm
    I see. Still flawed?
    no, that looks great now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Thank you. This is a very enjoyable process. I have 3 quesitons.

    1 - What is the proper way of testing the design? I am this far already and it looks a bit messy i can't easily follow.

    2 - Is it a good idea to make both columns as unique key in tables such as orders_AND_type, orders_AND_type and cart_AND_product? This is exact same situation as the other thread natural versus surrogate key.

    3 - In natural versus surrogate key situations, what is the right naming style. You see I am using AND which doesn't look very nice.

    Thank you
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    1 - there are several testing strategies you can use

    first is the "eyeball" test -- look at it, critique it, check for errors, analyze it, ask "what happens if...?" questions, etc.

    second is to populate it with as realistic data as you can come up with (i.e. not using a test data generator, which will fill nonsense data into all tables thus revealing nothing)

    third is to write all the queries that your application is going to need... inserts, updates and deletes as well as selects


    2 - you completely lost me on this ... why would an order have more than one type or more than one status at a given time?

    if you really do have a many-to-many relationship, then yes, you would make the primary key a composite key and not use a surrogate key at all

    3 - naming style is whatever you wish


    EDIT: sorry, that p.s. was needlessly harsh and insensitive, and it has been removed
    Last edited by r937; April 15th, 2013 at 09:07 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo