Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Posts
    59

    Exclamation Help with database design problem

    Hey all i am in need of some help/advice on how to go about making this table for a project of mine.

    It will be a coupon book database. The way the coupons are saved into the database is like this:
    Code:
    -----------------------------------------------------
    | Level | CoupPic | ExpDate | QTY | Company | FunR |
    -----------------------------------------------------
    There are 3 levels to coupons. Basic, Silver, and Premium. Each will have a saved picture in the db how the coupon will look.

    The Expdate is when the coupon will be voided (and deleted from the database).

    The QTY is how many times the user is able to print out that particular coupon.

    Company and FunR (fun raiser) are self explanatory.

    Here is what the coupon db would look like populated with some data.
    Code:
    -------------------------------------------------------------------------
    | Level  |   CoupPic | ExpDate | QTY | Company |            FunR        |
    -------------------------------------------------------------------------
    | Basic  |    (pic)  | 02/22/09|   5 |   Bobs  |            na          |
    | Premium|  (pic)    | 05/10/09|  10 |Tacobell | Little League baseball |
    | Basic  |    (pic)  | 10/02/09|   1 |   temp2 |            na          |
    | Basic  |    (pic)  | 04/20/09|   7 |   temp3 |        something       |
    | Silver |    (pic)  | 12/20/09|  11 |   temp3 |            na          |
    There can be an unlimited amount of coupons per each level. Basic could have 100 coupons, Silver could have 150 and Premium could have 500.

    Now for the users who will be getting these coupons. This is where i am having problems coming up with a solution. How would i be able to track how many times the user prints out a selected coupon from their purchased level if i do not know how many coupons will end up in the levels?

    Here's what i am talking about:
    Code:
    ------------------------------
    | Name | EmailAd | CoupLevel |
    ------------------------------
    Say user BOB with an email address of bob@here.com has level Basic. So lets say the Basic database houses about 50 company coupons.

    Company A has a QTY of 5.
    Company B has a QTY of 2.
    Company C has a QTY of 10.
    etc etc...

    How can i keep track of each coupon in the level that the user prints out if the coupon db is unlimited on how many coupons enter and exit?

    Hopefully i have explained it enough in order for you all to understand If not, please feel free to ask more questions about it.

    Thanks for your time,
    David

  2. #2
    Join Date
    Jul 2003
    Posts
    59
    Nobody, or am i not explaining it clearly enough?

    David

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by StealthRT
    How can i keep track of each coupon in the level that the user prints out if the coupon db is unlimited on how many coupons enter and exit?
    By recording that in a table with the user name and information for each coupon I guess. Why would that be a problem?

  4. #4
    Join Date
    Jul 2003
    Posts
    59
    Well are you saying to just add a table each time they print off a particular coupon?

    Here's what i think you are talking about:
    Code:
    ----------------------------------
    | Name | EmailAd     | CoupLevel |
    ----------------------------------
    | BOB  | me@g.com    | Silver    |
    Ok then a user prints off a coupon called "Tacobell".. Is this what you are talking about?
    Code:
    --------------------------------------------------------
    | Name | EmailAd     | CoupLevel | Coup1    | Coup1QTY |
    --------------------------------------------------------
    | BOB  | me@g.com    | Silver    | Tacobell | 1        |
    Then the user prints off another coupon thats called "Kystal".
    Code:
    --------------------------------------------------------
    | Name | EmailAd     | CoupLevel | Coup1    | Coup1QTY | Coup2    | Coup2QTY |
    --------------------------------------------------------
    | BOB  | me@g.com    | Silver    | Tacobell | 1        | Kystal   | 1        |
    Then the user again prints out a coupon for "Tacobell"...
    Code:
    --------------------------------------------------------
    | Name | EmailAd     | CoupLevel | Coup1    | Coup1QTY | Coup2    | Coup2QTY |
    --------------------------------------------------------
    | BOB  | me@g.com    | Silver    | Tacobell | 2        | Kystal   | 1        |
    Is that what you are getting at? Or am i way off?

    David

  5. #5
    Join Date
    Jul 2003
    Posts
    59
    Question:

    How would i go about using the SQLQuery to check for the coup1/coup1qty?
    Code:
    SELECT * FROM coupusers WHERE email = '" & theemail & "' AND ...?
    David

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You are way off what I meant. You only need one column for coupon, one for quantity and one for whatever is the key of user.

    Have you actually designed a database before? Are you familiar with design principles like Normal Forms and Orthogonal design? If not, I suggest you take a course or study a book before go much further.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by dportas
    You are way off what I meant. You only need one column for coupon, one for quantity and one for whatever is the key of user.

    Have you actually designed a database before? Are you familiar with design principles like Normal Forms and Orthogonal design? If not, I suggest you take a course or study a book before go much further.
    I assumed he already was taking a course, and this was part of the coursework
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2003
    Posts
    59
    You both are incorrect. Don't worry about it. I appreciated your help but i just did it the long way around and made 200 fields to hold the values.

    David

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by StealthRT
    i just did it the long way around and made 200 fields to hold the values.
    I suspect it won't be long before you regret that.

  10. #10
    Join Date
    Jul 2003
    Posts
    59
    Its the only way i can figure out how to go about doing what i need to do.

    David

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Please explain what is wrong with my suggestion. It is much simpler to build and to query.

  12. #12
    Join Date
    Jul 2003
    Posts
    59
    I do not see how you way is going to accomplish what i need it to do...

    Here is my code in VB:
    Code:
    Do Until x = 201
            If rst.Fields("c" & x) = theUID Then
                'Found it in the DB
                
                If rst.Fields("c" & x & "qty") = theQTY Then
                    MsgBox "Already at max"
                    Exit Do
                Else
                    rst.Fields("c" & x & "qty") = CInt(rst.Fields("c" & x & "qty") + 1)
                    rst.Update
                    Exit Do
                End If
            ElseIf rst.Fields("c" & x) = "na" Then
                rst.Fields("c" & x) = theUID
                rst.Fields("c" & x & "qty") = 1
                rst.Update
                Exit Do
            End If
            x = x + 1
        Loop
    As you can tell, i have fields named c1-c200 and c1qty-c200qty for each user in the database. I can not see how using one column for the name and another for the qty would be able to store 200 entries.

    David

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by StealthRT
    I can not see how using one column for the name and another for the qty would be able to store 200 entries.
    By creating one row for each entry instead of one column. That way you only have a single column for each attribute that you need to reference in your code and in queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •