SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table with multiple pk and join

    Hi,
    I'm working on a sql scheme of my customer
    I mean I've to set up an ecommerce with the data
    that the customer pull out from his intrenal csm.

    He turned out with this ugly table

    Code SQL:
    CREATE TABLE `pricelist_customer` (
    	`customer_id` VARCHAR(255) NOT NULL,
    	`product_id` CHAR(8) NOT NULL,
    	`qty_start` INT UNSIGNED NOT NULL,
    	`qty_end` INT UNSIGNED NOT NULL,
    	`price` DECIMAL(10,2) NOT NULL,
    	PRIMARY KEY (`customer_id`,`product_id`,`qty_start`,`qty_end`),
    	CONSTRAINT `fk_pricelist_customer_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    	CONSTRAINT `fk_pricelist_customer_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`email`) ON DELETE CASCADE ON UPDATE CASCADE
    )ENGINE=InnoDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;


    In my opinion you should use an id autoincrement simply, shouldn't you ?
    I've just a lot of troubles thinking to use a join statement as well with a table
    like that.
    Before arguing with my customer I'd like to know your opinion

    Bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    depends entirely on what tables you're going to be joining

    note that if you did have an auto_increment column, you would then have to have
    Code:
    UNIQUE (`customer_id`,`product_id`,`qty_start`,`qty_end`)
    so the auto_increment is overhead unless you have a child table of this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •