how to design database like this

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

how to design database like this

Author

Topic

EK
Starting Member

2 Posts

Posted - 2007-08-14 : 09:20:57

i want to create database to store product information and my application can create promotion of product

eg:
buy A get A free
buy A get B for 50%

thanks for help,
EK

pootle_flump

1064 Posts

Posted - 2007-08-14 : 10:09:25

What have you got so far? Your examples are about step #562 but it looks like you have just got to step #1 so far (#1 is deciding that you want a database).

EK
Starting Member

2 Posts

Posted - 2007-08-14 : 11:00:15

i have design db like below

table_product

id | name | price
1 | AAA | 10
2 | BBB | 20

table_transaction

id | product_id | quantity
1 | 1 | 5
2 | 2 | 2

table_bill

id | trans_id
1 | 1
1 | 2

but i want to create new feature in my program that can create promotion that i have explain in #1

forgive my english (i'm can't write it so well)
thank,
EK

pootle_flump

1064 Posts

Posted - 2007-08-14 : 11:21:51

That's ok EK - the English is fine - and it makes more sense now I know you have some structure. I am about to leave work & will look again later :)

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 16:55:00

ever hear of computed columns or triggers

Ashley Rhodes

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-16 : 10:47:48

triggers would be the best approach in my opinion...

use the the inserted value from the trigger

select @varname = column_name from inserted -- something like this..

SlayerS_`BoxeR` + [ReD]NaDa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 11:05:03

ah, the typical retail industry requirement. Trade Offer / Discount, PWP, PWD, mix and match etc.

Don't use trigger. It will only create unnecessary overhead. Computed column will not work well also.

The logic (depending on the requirement) might be too complicated to be preformed on trigger. It is best to use stored procedure to do this. Use the stored procedure to insert record into your transaction table and code the promotion logic there. When a BUY record is inserted to the transaction table, reference your promo table and if it fulfilled the promo requirement, insert the additional promo record (free or @ discounted price) into the transaction table.

Hope i made it clear here.

KH
[spoiler]Time is always against us[/spoiler]

recontrasalo
Starting Member

12 Posts

Posted - 2008-03-06 : 14:22:30

im new in data modeling. So don't yell at me.

drop table product
create table Product
(productID int primary key,
productName varchar(50),
productCategory varchar(100),
productInPromotion bit)

drop table promotion
create table Promotion
(promotionID int primary key,
promotionDetails varchar (100) null,
productID int references Product(productID) )

insert Product
select 1, 'A', 'Candy', 1 union all
select 2, 'B', 'Chocolate', 1 union all
select 3, 'A', 'Chocolate', 0

insert Promotion
select 10, 'Get 2nd one free', 1 union all
select 11, 'Get 2nd one 50% off', 2 union all
select 12, null, 3

select a.productName, b.promotionDetails, a.productCategory
from product a
join promotion b
on a.productID = b.productID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-07 : 03:07:12

recontrasalo,

please start a new thread for your question

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources