Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Database Structure

Posted on 2012-08-13
Medium Priority
Last Modified: 2012-08-28
I am building a database and have a question.  I'm a newbie so please be as "Newbie Friendly" as possible.

I have a DB that records orders from a shopping cart. The shopping cart Sells redeemable coupons.  I am a little confused on how to handle multiple units of one item.
Item A has 10 coupons valued @$10 a piece and also 5 coupons valued at $5 a piece.  So the order contains 15 items total.  Each coupon is redeemable.  Would the best way to do be Have an associated table with all the coupon values ordered by Order ID?

Not sure if this makes sense?
Question by:rgranlund
  • 4
  • 3
  • 3
  • +1
LVL 59
ID: 38288866
<<Would the best way to do be Have an associated table with all the coupon values ordered by Order ID?>>

 That's the way to do it.  What you need to decide though is if you want to end up with 15 records (one record per coupon)

 you have one record per redeem with a qty:

Item A  10 @ $10
Item A    5 @ $  5

 If there is nothing to distinguish one coupon from another (ie. no serial number) outside of the dollar amount, then I would use the latter.

LVL 111

Expert Comment

by:Ray Paseur
ID: 38288932
There's a retail concept called a Stock-Keeping Unit, or SKU.  It is the smallest unit anyone can order for a given item.  If the smallest unit you sell is collectively this...

An item with 10 coupons valued @$10 a piece and 5 coupons valued @$5 a piece

... then you would have one SKU for that unit, and the unit has a face value of $125.

However, if the client could order one coupon valued at $10 and/or one coupon valued at $5, you would need at least two additional SKUs to describe these smaller atomic orders.

The thing your design should seek to avoid is selling combinations that you do not want to sell.  For example, let's say you do not want to sell less than 3 $10 coupons.  The minimum order for this SKU would be 3.  The incremental order might be 1.  Then your programming can check to see that the minimum is satisfied for each order.

Does that make sense?  Not sure if I am explaining it well.

Author Comment

ID: 38288943

Each coupon is redeemable individually and each coupon has its own serial number.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 38288950
What JDettman posted sounds good but after reading both I wanted to make sure everyone is on the same page and maybe I just misunderstood.  rgranlund your post makes it sound the like the site is selling Redeemable Coupons where JDettman sounds like he is applying Redeemable Coupons to the item?

 Also, you say Item A if my assumption about rgranlund is correct then would that be a bundle of Redeemable Coupons with quantity and value as you mention in the post or was the 5 @ $5 supposed to be Item B?  

If that was the case then you may need to add an additional column to JDettman table that stores the order ID:
100        |A          |10
100        |B          |5

100        |ABC          
100        |BDE
LVL 59
ID: 38289002
<<Each coupon is redeemable individually and each coupon has its own serial number. >>

 Your going to want 15 records then, and yes the table would have to include order ID

Order ID    Coupon Serial number   Item   Value
1                123456                            ItemA      $10
1                123457                            ItemA      $  5
1                123458                            ItemB      $15
2                123459                            ItemA      $  5
2                123460                            ItemC      $20

 assuming that the Coupon is really the thing were tracking here and that a coupon could be applied to any item and for any dollar amount.

 In fact really Coupons should be a seperate table and then referenced on the order.

LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38289033
<<In fact really Coupons should be a seperate table and then referenced on the order.>>


CouponID  - Autonumebr PK
DateCreated - D/T

and then tblOrders
OrderID - Autonumber - PK
DateCreated - D/T

tblOrderDetail - one record per order/coupon
OrderDetailID - Autonumber PK
OrderID - FK to tblOrders
CouponID - FK to tblCoupons

  But really I think we'd need to know a little more.  I'd only do the above if coupons were something generated and then sold.

  If they are generated on the fly when someone places an order and you can never run out, then I might do it the other way.

  All depends on what you want to know about coupons and how they get created.

LVL 111

Expert Comment

by:Ray Paseur
ID: 38289159
It doesn't matter whether each coupon is separately redeemable.  It matters whether you want to sell them separately.  Consider the labor and shipping difference between an order for 50 coupons and 50 orders to 50 different addresses for one coupon.

You might want to get a professional DBA to look at the business model and suggest the table designs.

Author Comment

ID: 38289859
So, how would I construct a DB table and fields for:

Order#1 contains 5 coupons, each with a unique serial number
Order#2 contains 2 coupons, each with a unique serial number

Each order has its own Order ID Number, unique to the total order.

How would I record that into the DB with each of the coupons being referenced?
LVL 111

Expert Comment

by:Ray Paseur
ID: 38289936
You would probably want to have a table of coupons - one row for each coupon.  And a table of orders - one row for each order.  And a junction table.  In each row of the junction table, you will have both sides of the transaction -- the key of an order row and the key of a coupon row.  This table shows the relationship between the orders and the coupons, hence the name "relational data base."

Expert Comment

ID: 38289952
Diagram of proposed tables.As anything in life there are a lot of ways to do this.  Another question to bring up is do you know or does it matter at time of checkout if the coupon serial #s are assigned or is this something that is done at order fulfuillment?  I would think you may not want to worry about associating coupon serial number this early in the process but that is up to your business logic.

And regarding the post about 50 coupons to a single address vs 50 coupons to 50 different addresses.  That seems more a matter of are you allowing multiple addresses for an order.  Typically at most consumer sights you pick a single address for the whole order but individual addresses could be accomplished as well.

Again I like where JDettman is headed I think I would add another table or two I added a picture to show.

Author Comment

ID: 38290010
@ ALL I think where I am confused is how to write the foreach statement to separate out the amount of coupons and the right each coupon into the DB.
LVL 59
ID: 38290473
<<@ ALL I think where I am confused is how to write the foreach statement to separate out the amount of coupons and the right each coupon into the DB. >>

 I think you'll end up with what Ray suggested, but before we go too far, none of us really understand what these coupon's represent and more importantly, what facts you need to know about them.

 For example, do you need to know things like:

DateCreated - Date coupon was created
DateIssued - Date was sold on an order
DateRedeemed - Date coupon was redeemed

  What I would suggest is using the traditional approach:

1. Identify what information you want out of the database (reports and informational queries) and the business functions required (i.e. ability to buy coupons).

2. To get #1, what facts (fields of data) do you need to have?

  Once you have all the fields, then you start grouping them together by what "thing" they belong to.   For example,  "order", "coupon", and "customer" all are different things.  Each thing will become a table.

Customer Name is a piece of data about a customer, so it will go in the customer table.  It does not belong in the order table.

 I think if you walk through that excerise you'll be amazed how quickly you'll get a sense of what form your database needs to take.  Once you have that, we can help you refine it here.


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question