3

How would you design your database to achieve this functionality?

Consider a scenario where we want to create a product relation (package)...

Say that we create a ProductTbl

prod_id   prod_name    prod_fee
1         prepaid-A    19 usd
2         prepaid-B    29 usd
3         prepaid-C    39 usd
4         internet      9 usd
5         mms           1 usd
6         email         3 usd

We want to offer a product package that provides better fees for customer.

E.g. if customer choose prepaid-A + internet + mms they will have a pkg fee by 25 usd (instead of 29 usd).

DESIGN

  • A pkg with same combination should only exist once.
  • A pkg can have unlimited number of products, if possible. (Normally will be 2-4).

E.g. of a pkg combination

ProdPkgTbl

ppkg_id   prod_name    prod_fee
1         prepaid-A    25 usd
1         mms          25 usd
1         internet     25 usd

2         internet     10 usd
2         email        10 usd

3         prepaid-C    45 usd
3         internet     45 usd
3         mms          45 usd
3         email        45 usd

This means that combination

  • pkgid 1 -> prepaid-a, mms, and internet for a pkg fee of 25 usd
  • pkgid 2 -> internet and email together for a fee of 10 usd
  • pkgid 3 -> prepaid-c, internet, mms, email all together for 45 usd

So if a customer got prepaid-c + internet only they not get the deal for 45 usd, they will pay normal fee of 39 + 9 = 48 usd.

NOW THIS TABLES are just for demo, I would like to get your input and advice to model it in a right direction.

We will need first to select-query a customer current fee plan (based on used products/services) and after that look into the product pkg and see if pkg deal is valid for them or not.

(How to do such selection? on pkg table?) How to have a good db model for this?

Please advice with urls, hints, anything with value to push me into the right direction.

Great thanks.

MrSimpleMind
  • 7,890
  • 3
  • 40
  • 45

2 Answers2

2

You are selling goods or services from a catalog. You can also sell combinations of the above. This is called a Marketing Package (or Bundle or Combo).

A marketing package is also sold from the catalog. It can be comprised of goods, services, or other marketing packages.

The price is really different per order. You could set a base price in the good table, a monthly price in the service table, and a base price in the marketing package table.

enter image description here

Community
  • 1
  • 1
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • Thank you Neil, I m trying to understand this model. And it sounds good with the idea of marketing package. But how to be sure that a marketing package only can be exist once. For example, we dont want to risk that good1+good2 are in packageA with feeA. And same good1+good2 are again in packageB with feeB. This scenario must never happen. – MrSimpleMind Feb 20 '13 at 22:09
  • any further comments or progress in this case? :) – MrSimpleMind Mar 03 '13 at 10:09
  • @MrSimpleMind you would need to use a check constraint with relational division to make sure that identical marketing packages don't exist. http://dba.stackexchange.com/questions/45829/what-is-the-name-of-this-type-of-query-and-what-is-an-efficient-example – Neil McGuigan Jul 31 '13 at 21:47
  • THANK YOU very much Neil for all your input! I will let you know the progress later when we start the project... – MrSimpleMind Aug 07 '13 at 14:38
  • Sorry, I mean you should used a trigger, not a CHECK – Neil McGuigan Jan 22 '14 at 18:45
0

I was hoping for some input regarding this but maybe the question is to "messy" to answer. :o)

My model right now is like this:

ProductTbl

prod_id   prod_name    prod_fee
1         prepaid-A    19 usd
2         prepaid-B    29 usd
3         prepaid-C    39 usd
4         internet      9 usd
5         mms           1 usd
6         email         3 usd

ProductPkgTbl

ppkg_id   prod_id      prod_name 
1         1            prepaid-A 
1         5            mms       
1         4            internet  

2         4            internet  
2         6            email     

3         3            prepaid-C 
3         4            internet  
3         5            mms       
3         6            email

ProductPkgFeeTbl

ppkg_id   ppkig_fee
1         25
2         10
3         45

But there are some flaws in above, and it is that someone could insert same products in different pkg and with different fees.

I really can't figure out how to be sure that a pkg is unique, with sql. Well, of course a programming language / stored procedure / triggers etc can make the check ! But I want to have a design that is 100% flawless.

Please push me in the right direction with your experience in matters like this...

MrSimpleMind
  • 7,890
  • 3
  • 40
  • 45