CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-databasedatabase-schema
carlplusplus
carlplusplus

Reputation: 77

Database design with creating a product that has two prices

I'm creating a database design for our water refilling system and I'm just new to databases. I am stuck with creating table that provides two different prices for a product. To further explain my problem, here's an example, a product's ('5 GALLON') price changes when it is delivered or bought on point by a customer. For example a delivered ('5 GALLON') is 45 pesos while a bought on point gallon is only 40 pesos. Can someone help me please?

Here's my codes so far

create table Product (
    product_id int primary key,
    prodtype_id int,
    product_name varchar(55),
    product_quantity int
)

-----NOT SURE IF THESE TWO TABLES ARE CORRECT
create table DeliveryPrice (
    prod_id int,
    product_price money,
    foreign key (prod_id) references Product
)

create table OnPointPrice(
    prod_id int,
    product_price money,
    foreign key (prod_id) references Product
)

Upvotes: 0

Views: 476

Answers (2)

carlplusplus
carlplusplus

Reputation: 77

So is this correct?

create table Product( product_id int primary key, product_name varchar(55) not null, product_quantity int not null, pickup_price money not null, delivery_price money not null )

create table Customer( customer_id int primary key, customer_name varchar (255) not null, customer_address varchar(200), customer_phone int ) create table INVOICE( inv_number int primary key, customer_id varchar(5), foreign key (customer_id) references Customer, inv_date date not null, bought_mode char(10) not null )

create table LINE( INV_NUMBER int, foreign key (INV_NUMBER) references INVOICE, LINE_NUMBER INT not null, PRIMARY KEY (INV_NUMBER, LINE_NUMBER), line_quantity int not null, line_price money not null )

Upvotes: 0

Tom H
Tom H

Reputation: 47464

You're likely better off just having the two prices in the Product table. They are attributes of the product, so that's where they belong.

Also, you should specify which columns are NOT NULL in your database (which should be most of them).

Upvotes: 1

Related Questions