Help with database design problem

StealthRT · #1 (**permalink**) 03-31-09, 15:38

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

StealthRT · #2 (**permalink**) 04-01-09, 00:24

Nobody, or am i not explaining it clearly enough?

David

dportas · #3 (**permalink**) 04-01-09, 02:39

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?

StealthRT · #4 (**permalink**) 04-01-09, 18:52

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

StealthRT · #5 (**permalink**) 04-01-09, 23:00

Question:

How would i go about using the SQLQuery to check for the coup1/coup1qty?

Code:

SELECT * FROM coupusers WHERE email = '" & theemail & "' AND ...?

David

dportas · #6 (**permalink**) 04-02-09, 02:44

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.

healdem · #7 (**permalink**) 04-02-09, 03:24

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

StealthRT · #8 (**permalink**) 04-02-09, 14:20

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

dportas · #9 (**permalink**) 04-02-09, 14:26

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.

StealthRT · #10 (**permalink**) 04-02-09, 14:51

Its the only way i can figure out how to go about doing what i need to do.

David

dportas · #11 (**permalink**) 04-02-09, 14:55

Please explain what is wrong with my suggestion. It is much simpler to build and to query.

StealthRT · #12 (**permalink**) 04-02-09, 15:17

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

dportas · #13 (**permalink**) 04-02-09, 15:26

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech