You could always do something like this:
Create a users
table to store all of your user info:
create table users
(
id int,
user_name varchar(50)
);
insert into users values
(1, 'John'),
(2, 'Jill'),
(3, 'Bob'),
(4, 'Sam');
You state that you might need to apply this type of tracking for multiple applications. So you can create an application
table to store the app name and any details.
create table applications
(
id int,
app_name varchar(50)
);
insert into applications values
(1, 'App 1'),
(2, 'App 2'),
(3, 'App 3');
Next create a categories
table to store the info on each of the categories that you want to track by the users. This table will include a column that will contain the application id. This table will contain a foreign key relationship to the applications
table. This allows you to have some flexibility in having the same category in multiple apps.
create table categories
(
id int,
cat_name varchar(100),
a_id int
);
insert into categories values
(1, 'Cat 1', 1),
(2, 'Cat 2', 1),
(3, 'Cat 3', 2),
(4, 'Cat 4', 3);
Finally, create the users_categories
table which will contain the stats that you want to track, including user
, category
and then the action_date
(the date the event took place).
create table users_categories
(
u_id int,
c_id int,
action_date datetime
);
insert into users_categories values
(1, 1, current_timestamp()),
(2, 3, '2012-01-15 10:15'),
(3, 4, '2012-08-24 12:48'),
(1, 2, '2012-06-08 08:50');
Then to query the data, you would use something similar to this:
select *
from users u
left join users_categories uc
on u.id = uc.u_id
left join categories c
on uc.c_id = c.id
left join applications a
on c.a_id = a.id
And the results would contain (See SQL Fiddle with Demo):
ID | USER_NAME | U_ID | C_ID | ACTION_DATE | CAT_NAME | A_ID | APP_NAME
------------------------------------------------------------------------------------------
1 | John | 1 | 1 | '2012-09-28 06:47:53' | Cat 1 | 1 | App 1
1 | John | 1 | 2 | '2012-06-08 08:50:00' | Cat 2 | 1 | App 1
2 | Jill | 2 | 3 | '2012-01-15 10:15:00' | Cat 3 | 2 | App 2
3 | Bob | 3 | 4 | '2012-08-24 12:48:00' | Cat 4 | 3 | App 3
4 | Sam | (null) | (null) | (null) | (null) | (null) | (null)
A few other suggestions, you could technically remove the a_id
from the categories
table and create a join table between categories and applications, similar to this:
create table categories_applications
(
c_id int,
a_id int
);
insert into categories_applications values
(1, 1),
(2, 3),
(3, 1),
(1, 2);
This would lead to one additional join in your queries, but the results would be the same:
select *
from users u
left join users_categories uc
on u.id = uc.u_id
left join categories c
on uc.c_id = c.id
left join categories_applications ca
on c.id = ca.c_id
left join applications a
on ca.a_id = a.id
No.
You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else.
Withdrawals should be in a single table, and should have its own details as necessary ( paypal out-payment, bank out-payment etc, if applicable in future). Because withdrawals can be more than one - a guy who has $100 in his account can withdraw 4 $25 withdrawals in differing times. Which will have to be paid out in its own payment method and confirmed in its own fashion. Therefore withdrawals is a different identity.
And all the searches involving these entities will happen in that fashion too - you cant search a withdrawal IN RELATION TO two different purchases in Purchases. Or vice verse. They are independent entities which are only linked by user_id, and very loosely by account balance - the latter only as a limiting factor for the other.
Userts table is solid as it is and needs no change.
Best Answer
If I were to do this, I'd use SQL Server, with 3 tables.
I'd create a couple of stored procedures, however you could easily do these in the application code if desired. Both procedures use a single atomic statement that negates the need for an explicit transaction since either the entire transaction will commit, or it will entirely roll back.
I tend to recommend using stored procedures for DML operations wherever possible since this allows you to make database level DDL changes without the requirement of deploying new binaries everywhere.
The first proc simply inserts a new row into the
dbo.Status
table, and returns a resultset including the newRequestID
, theCurrentStatusID
(which is set to 1), and theCurrentStatusDate
which is set to the current date and time usingGETDATE()
:The second proc allows us to do 2 operations in one atomic unit. First, it updates the
CurrentStatusID
andCurrentStatusDate
columns indbo.Requests
. Then, using theOUTPUT
clause, it inserts the old values fromdbo.Requests
intodbo.RequestStatusHistory
.Insert some sample data:
Show the results:
When the client wants to see status history, you could provide those details using a reasonably flexible stored procedure such as:
Running the
RequestHistory
proc against the sample data: