Populating database with price range values

Question

i am stuck on trying to populate a given table that has a price range. I've tried the decimal data type, numeric, float, but nothing has worked for me. More specifically, i don't quite understand how to write the script for the "Amount of Order" column since it has the values in a price range.

The table:

and the script that i attempted..

  DROP TABLE MAIL_ORDERS;
CREATE TABLE MAIL_ORDERS (
amountofOrder MONEY not null PRIMARY KEY,
orderID INT not null);

DROP TABLE DELIVERY_DETAILS;
CREATE TABLE DELIVERY_DETAILS (
deliveryID INT not null PRIMARY KEY,
regular MONEY not null,
rush MONEY not null,
express MONEY not null,
amountofOrder MONEY not null,
CONSTRAINT fk_deliverydetails_mailorders FOREIGN KEY (amountofOrder)
REFERENCES MAIL_ORDERS (amountofOrder));

INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($0.00 - $15.00, 1);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($15.01 - $30.00, 2);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($30.01 - $45.00, 3);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($45.01 - $65.00, 4);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($65.01 - $90.00, 5);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($90.01 - $125.00, 6);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($125.01 - $200.00, 7);
INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($200.01 - null, 8);

INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (1, $4.95, $9.95, $17.45, $0.00 - $15.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (2, $5.95, $10.95, $18.45, $15.01 - $30.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (3, $7.95, $12.95, $20.45, $30.01 - $45.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (4, $9.95, $14.95, $22.45, $45.01 - $65.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (5, $11.95, $16.95, $24.45, $65.01 - $90.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (6, $13.95, $18.95, $26.45, $90.01 - $125.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (7, $14.95, $19.95, $27.45, $125.01 - $200.00);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, amountofOrder)
VALUES (8, $16.95, $21.95, $29.45, $200.01 - null);

SELECT * FROM MAIL_ORDER
SELECT * FROM DELIVERY_DETAILS

Answer 1

If the data value is a range, I think you design them with two column will be better for processing, for example ,you can change the the table MAIL_ORDERS like the following, when process data such as query use start dan end value, the new cloumn AmountofOrder is a computed cloumn for display and primary key.

CREATE TABLE MAIL_ORDERS (
    AmountofOrderStart MONEY,
    AmountofOrderEnd MONEY,
    AmountofOrder AS ISNULL('$'+LTRIM(AmountofOrderStart),'Lesst than ')+ISNULL(' - $'+LTRIM(AmountofOrderEnd), ' +'),
    orderID INT not NULL,
    CONSTRAINT [PK_MAIL_ORDERS] PRIMARY KEY CLUSTERED (AmountofOrder)
)
INSERT INTO MAIL_ORDERS(AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($0.00, $15.00, 1);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($15.01 , $30.00, 2);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($30.01 , $45.00, 3);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($45.01 , $65.00, 4);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($65.01 , $90.00, 5);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($90.01 , $125.00, 6);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($125.01 , $200.00, 7);
INSERT INTO MAIL_ORDERS (AmountofOrderStart,AmountofOrderEnd, orderID) VALUES ($200.01 , null, 8);

SELECT * FROM MAIL_ORDERS ORDER BY AmountofOrderStart

AmountofOrderStart    AmountofOrderEnd      AmountofOrder                                                                         orderID
--------------------- --------------------- ------------------------------------------------------------------------------------- -----------
0.00                  15.00                 $0.00 - $15.00                                                                        1
15.01                 30.00                 $15.01 - $30.00                                                                       2
30.01                 45.00                 $30.01 - $45.00                                                                       3
45.01                 65.00                 $45.01 - $65.00                                                                       4
65.01                 90.00                 $65.01 - $90.00                                                                       5
90.01                 125.00                $90.01 - $125.00                                                                      6
125.01                200.00                $125.01 - $200.00                                                                     7
200.01                NULL                  $200.01+                                                                              8

Answer 2

I would definitely recommend 3 tables for something like this so that if you ever need to change shipping options, date ranges, individual prices, expand to be dependent on yet another table it will be flexible to change without having to do a huge code refactor.

Anyway, I would Nth normalize it to something like:

CREATE TABLE AmountRanges (
    AmountRangeId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,StartRange NUMERIC(15,4) NOT NULL
    ,EndRange NUMERIC(15,4) NOT NULL
);

CREATE TABLE ShippingOptions (
    ShippingOptionId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,StartRange INT NOT NULL
    ,EndRange INT NOT NULL
    ,Description VARCHAR(50)
); 

CREATE TABLE ShippingPrices (
    ShippingPriceId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,AmountRangeId INT NOT NULL
    ,ShippingOptionId INT NOT NULL
    ,Price NUMERIC(15,4) NOT NULL
    ,FOREIGN KEY (AmountRangeId) REFERENCES AmountRanges (AmountRangeId)
    ,FOREIGN KEY (ShippingOptionId) REFERENCES ShippingOptions (ShippingOptionId)
);


INSERT INTO AmountRanges (StartRange, EndRange) VALUES (0,15),(15.01,30),(30.01,45),(45.01,65),(65.01,90);
INSERT INTO ShippingOptions (StartRange, EndRange,Description) VALUES (7,10,'Regular Delivery 7-10 days')
,(4,5,'Rush Delivery 4-5 Busienss Days'),(1,2,'Express Delivery 1-2 Business Days');
INSERT INTO ShippingPrices (AmountRangeId,ShippingOptionId,Price) VALUES (1,1,4.95),(1,2,9.95),(1,3,17.45)
,(2,1,5.95),(2,2,10.95),(2,3,18.45),(3,1,7.95),(3,2,12.95),(3,3,20.45)
,(4,1,9.95),(4,2,14.95),(4,3,22.45),(5,1,11.95),(5,2,16.95),(5,3,24.45);


SELECT
    p.ShippingPriceId
    ,a.StartRange as AmountStartRange
    ,a.EndRange as AmountEndRange
    ,o.StartRange as DaysStartRange
    ,o.EndRange as DaysEndRange
    ,o.Description
    ,p.Price
FROM
   ShippingPrices p
   INNER JOIN AmountRanges a
   ON p.AmountRangeId = a.AmountRangeId
   INNER JOIN ShippingOptions o
   ON p.ShippingOptionId = o.ShippingOptionId
;   

here is a link showing how it works http://rextester.com/l/mysql_online_compiler

and if you really want the table in the exact same format all you have to do is pivot on the shipping options. I would recommend conditional aggregation in this case to do the pivot.

Answer 3

Ok... Well

INSERT INTO MAIL_ORDERS (amountofOrder, orderID) VALUES ($15.01 - $30.00, 2); 
--Cristina:  Actually you are substracting $15.01 - $30.00 = -14.99

You can not do that, you will need two fields, and the relationship it's not too good.

Normally we set to int the primary keys.

Check the example:

DROP TABLE MAIL_ORDERS;
create TABLE MAIL_ORDERS (
amountofOrderInit MONEY not null,
amountofOrderEnd MONEY null,
orderID INT not null primary key);

      go

DROP TABLE DELIVERY_DETAILS;
CREATE TABLE DELIVERY_DETAILS (
deliveryID INT not null PRIMARY KEY,
regular MONEY not null,
rush MONEY not null,
express MONEY not null,
orderID int not null,
CONSTRAINT fk_deliverydetails_mailorders FOREIGN KEY (orderID)
REFERENCES MAIL_ORDERS (orderID));

go

INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($0.00 , $15.00, 1);
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($15.01 , $30.00, 2); --Cristina:  Actually you are substracting $15.01 - $30.00 = -14.99
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($30.01 , $45.00, 3); 
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($45.01 , $65.00, 4);
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($65.01 , $90.00, 5);
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($90.01 , $125.00, 6);
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($125.01 , $200.00, 7);
INSERT INTO MAIL_ORDERS (amountofOrderInit, amountofOrderEnd, orderID) VALUES ($200.01 , null, 8);

INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (1, $4.95, $9.95, $17.45, 1);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (2, $5.95, $10.95, $18.45, 2);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (3, $7.95, $12.95, $20.45, 3);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (4, $9.95, $14.95, $22.45,4);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (5, $11.95, $16.95, $24.45, 5);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (6, $13.95, $18.95, $26.45, 6);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (7, $14.95, $19.95, $27.45, 7);
INSERT INTO DELIVERY_DETAILS (deliveryID, regular, rush, express, orderID)
VALUES (8, $16.95, $21.95, $29.45, 8);


SELECT 
    mo.*
    ,cONCAT('$ ', mo.amountofOrderInit, ' - ' ,'$ ',mo.amountofOrderEnd) as amountofOrder --add column only for the query
FROM MAIL_ORDERS mo

SELECT 
    dd.* 
,CONCAT('$ ', mo.amountofOrderInit, ' - ' ,'$ ', mo.amountofOrderEnd) as amountofOrder --add column only for the query, in this case the info comes from the MAIL_ORDERS table
FROM DELIVERY_DETAILS dd     
    inner join MAIL_ORDERS mo     --Add a relationship between the tables
    on  dd.orderID = mo.orderID

So... What do you wanna do with these data?

I will use only one table, like this example:

declare @priceByAmount table(
    id int identity not null primary key ,
    initialAmount money not null,
    finalAmount money,
    reguarDelivery money not null,
    rushDelivery money not null,
    expressDelivery money not null
);

insert into @priceByAmount values (0,15,4.95,9.95,17.45)
insert into @priceByAmount values (15.01,30,5.95, 10.95, 18.45)
insert into @priceByAmount values (30.01,45, 7.95, 12.95, 20.45)
insert into @priceByAmount values (45.01,65,9.95, 14.95, 22.45)
insert into @priceByAmount values (65.01,90,11.95, 16.95, 24.45)
insert into @priceByAmount values (90.01,125,13.95, 18.95, 26.45)
insert into @priceByAmount values (125.01,200,14.95, 19.95, 27.45)
insert into @priceByAmount values (200.01,null,16.95, 21.95, 29.45)


select 
    --*
    CONCAT('$ ', initialAmount,  isnull(' to $' + convert(nvarchar(100),finalAmount),' + ')) as [Amount of Order]
    ,reguarDelivery [Regular Delivery 7-10 Days]
    ,rushDelivery [Rush Delivery 4-5 Business Days]
    ,expressDelivery [Express Delivery 1-2 Business Days]
from @priceByAmount