Database design challenge

Question

Hi Guys,

I'm creating an virtual stamp card program for the iphone and have run into an issue with implementing my database. The program essentially has a main points system that can be utitlized through all merchants (sort've like air miles), but i also want to keep track of how many times you've been to EACH merchant

So far, i have created 3 main tables for users, merchants, and transactions.

1) Users table contains basic info like user_id and total points collected.

2) Merchants table contains info like merchant_id, location, total points given.

3) Transactions table simply creates a new row for every time someone checks into each merchant, and records date-stamp, user name, merchant name, and points awarded.

So the most basic way to deal with finding out how many times you've been to each merchant is to query the entire transaction table for both user and merchant, and this will give me a transaction history of how many times you've been to that specific merchant(which is perfect), but in the long run, i feel this will be horrible for performance.

The other straightforward, yet "dumb" method for implementing this, would be to create a column in the users table for EACH merchant, and keep the running totals there. This seems inappropriate, as I will be adding new merchants on a regular basis, and there would need to be new columns added to every user for every time this happens.

I've looked into one-to-many and many-to-many relationships for mySQL databases, but can't seem to come up with something very concrete, as i'm extremely new to web/PHP/mySQL development but i'm guessing this is what i'm looking for...

I've also thought of creating a special transaction table for each user, which will have a column for merchant and another for the # of times visited. Again, not sure if this is the most efficient implementation.

Can someone point me in the right direction?

Answer 1

You're doing the right thing in the sense of thinking up the different options, and weighing up the good and bad for each.

Personally, I'd go with a MerchantCounter table which joins on your Merchant table by id_merchant (for example) and which you keep up-to-date explicitly.

Over time it does not get slower (unlike an activity-search), and does not take up lots of space.

Edit: based on your comment, Janan, no I would use a single MerchantCounter table. So you've got your Merchant table:

id_merchant        nm_merchant
12                 Jim
15                 Tom
17                 Wilbur

You would add a single additional table, MerchantCounter (edited to show how to tally totals for individual users):

id_merchant    id_user     num_visits
12             101         3
12             102         8
15             101         6007
17             102         88
17             104         19
17             105         1

You can see how id_merchant links the table to the Merchant table, and id_user links to a further User table.