How to design this?

I know this group is about programming, and there is no other group (or
is there?) about database design. So I'll just ask it here.
A product can be sourced from multiple suppliers (worldwide). So I came
up with the tables below.
CREATE TABLE Product (
ProductCode NVARCHAR(10) NOT NULL PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Supplier (
SupplierCode NVARCHAR(10) NOT NULL PRIMARY KEY,
SupplierName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE ProductSupplier (
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
..,
PRIMARY KEY (ProductCode, SupplierCode)
)
Now, for a product that is source from a supplier, I have multiple
prices, depending on the quantity I ordered. How would I model this? Is
the following correct?
CREATE TABLE ProductSupplierPrice (
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
Price MONEY NOT NULL,
Qty BIGINT NOT NULL,
PRIMARY KEY (ProductCode, SupplierCode)
)
Or, should I just have an identity column in ProductSupplier, and then
references it from ProductSupplierPrice? Like this:
CREATE TABLE ProductSupplier (
ProductSupplierId INT NOT NULL PRIMARY,
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
..,
? UNIQUE INDEX (ProductCode, SupplierCode)
)
CREATE TABLE ProductSupplierPrice (
ProductSupplierPriceId INT NOT NULL PRIMARY,
ProductSupplierId INT NOT NULL
REFERENCES ProductSupplier(ProductSupplierId),
Price MONEY NOT NULL,
Qty BIGINT NOT NULL,
PRIMARY KEY (ProductCode, SupplierCode)
)> CREATE TABLE ProductSupplierPrice (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
is incorrect

> CREATE TABLE ProductSupplierPrice (
> ProductSupplierPriceId INT NOT NULL PRIMARY,
> ProductSupplierId INT NOT NULL
> REFERENCES ProductSupplier(ProductSupplierId),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
is incorrect too because (as you've mentioned above) there are (possible)
several prices for each product from each supplier depending on quantity.
So, Qty column should also be a part of the primary key of
ProductSupplierPrice table. But you may add surrogate key column to this
table (not null'ed Identity with Unique index on it) for use in FK
constraints, if needed.
WBR, Evergray
--
Words mean nothing...
"Michael Wong" <nospam@.email.here> wrote in message
news:%23uJb5FzQGHA.5152@.TK2MSFTNGP10.phx.gbl...
>I know this group is about programming, and there is no other group (or is
>there?) about database design. So I'll just ask it here.
> A product can be sourced from multiple suppliers (worldwide). So I came up
> with the tables below.
> CREATE TABLE Product (
> ProductCode NVARCHAR(10) NOT NULL PRIMARY KEY,
> ProductName NVARCHAR(50) NOT NULL,
> ...
> )
> CREATE TABLE Supplier (
> SupplierCode NVARCHAR(10) NOT NULL PRIMARY KEY,
> SupplierName NVARCHAR(50) NOT NULL,
> ...
> )
> CREATE TABLE ProductSupplier (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> ...,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
> Now, for a product that is source from a supplier, I have multiple prices,
> depending on the quantity I ordered. How would I model this? Is the
> following correct?
> CREATE TABLE ProductSupplierPrice (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
> Or, should I just have an identity column in ProductSupplier, and then
> references it from ProductSupplierPrice? Like this:
> CREATE TABLE ProductSupplier (
> ProductSupplierId INT NOT NULL PRIMARY,
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> ...,
> ? UNIQUE INDEX (ProductCode, SupplierCode)
> )
> CREATE TABLE ProductSupplierPrice (
> ProductSupplierPriceId INT NOT NULL PRIMARY,
> ProductSupplierId INT NOT NULL
> REFERENCES ProductSupplier(ProductSupplierId),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )|||Thanks for the hint.
Evergray wrote:
>
> is incorrect
>
>
> is incorrect too because (as you've mentioned above) there are (possible)
> several prices for each product from each supplier depending on quantity.
> So, Qty column should also be a part of the primary key of
> ProductSupplierPrice table. But you may add surrogate key column to this
> table (not null'ed Identity with Unique index on it) for use in FK
> constraints, if needed.
>|||>> I have multiple prices, depending on the quantity I ordered. How would I
model this? <<
I know it is an example, but you might want to use reasonable data
types. Do you really expect a BIGINT quantity?
CREATE TABLE ProductQuantiyBreaks
(product_code CHAR(13) NOT NULL -- upc codes?
REFERENCES Products (product_code)
ON DELETE CASCADE
ON UPDATE CASCADE,
,supplier_id CHAR(10) NOT NULL --duns number
REFERENCES Suppliers(supplier_id),
ON DELETE CASCADE
ON UPDATE CASCADE,
low_qty INTEGER NOT NULL
CHECK(low_qty > 0),
high_qty INTEGER NOT NULL,
CHECK(low_qty < high_qty),
unit_price DECIMAL (12,5) NOT NULL,
PRIMARY KEY (product_code, supplier_id, low_qty));
Now fill in the discounts by (low_qty, high_qty) ranges so you can use
a BETWEEN predicate to locate the price of one unit. Never use
IDENTITY, since it is non-relational; never use MONEY since it is
proprietary and has weird math results.|||Yeah, that's definitely easier when querying with the low_qty and high_qty.
Just one question, having
PRIMARY KEY (product_code, supplier_id, low_qty), does that mean the
table ProductQuantiyBreaks is independent of table ProductSupplier?
Sorry to ask this, but I'm still new to these composite keys.
Thanks
--CELKO-- wrote:
>
> I know it is an example, but you might want to use reasonable data
> types. Do you really expect a BIGINT quantity?
> CREATE TABLE ProductQuantiyBreaks
> (product_code CHAR(13) NOT NULL -- upc codes?
> REFERENCES Products (product_code)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ,supplier_id CHAR(10) NOT NULL --duns number
> REFERENCES Suppliers(supplier_id),
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> low_qty INTEGER NOT NULL
> CHECK(low_qty > 0),
> high_qty INTEGER NOT NULL,
> CHECK(low_qty < high_qty),
> unit_price DECIMAL (12,5) NOT NULL,
> PRIMARY KEY (product_code, supplier_id, low_qty));
> Now fill in the discounts by (low_qty, high_qty) ranges so you can use
> a BETWEEN predicate to locate the price of one unit. Never use
> IDENTITY, since it is non-relational; never use MONEY since it is
> proprietary and has weird math results.
>|||having PRIMARY KEY (product_code, supplier_id, low_qty), does that
mean the table ProductQuantiyBreaks is independent of table
ProductSupplier? <<
It means that price breaks are VERY dependent on suppliers. In fact
that important; you need to pick the cheapest guy
product_code supplier_id low_qty high_qty unit_price
========================================
================
1234567890123 1111122222 1 10 10.00
1234567890123 3333344444 1 5 10.00
1234567890123 3333344444 6 10 9.50
Both suppliers are the same up to quantity 5, so I would need to make a
decision. After that, go with 3333344444.|||Ok, I got it to work with:
REFERENCES ProductSupplier(Productcode, SupplierCode)
--CELKO-- wrote:
> having PRIMARY KEY (product_code, supplier_id, low_qty), does that
> mean the table ProductQuantiyBreaks is independent of table
> ProductSupplier? <<
> It means that price breaks are VERY dependent on suppliers. In fact
> that important; you need to pick the cheapest guy
> product_code supplier_id low_qty high_qty unit_price
> ========================================
================
> 1234567890123 1111122222 1 10 10.00
> 1234567890123 3333344444 1 5 10.00
> 1234567890123 3333344444 6 10 9.50
> Both suppliers are the same up to quantity 5, so I would need to make a
> decision. After that, go with 3333344444.
>