Table with multiple pk and join

Databases
1

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 :frowning:


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 :slight_smile:

Bye

2

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

[B]UNIQUE [/B](`customer_id`,`product_id`,`qty_start`,`qty_end`)

so the auto_increment is overhead unless you have a child table of this table