database design for deposits /WITHDRAWAL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

database design for deposits /WITHDRAWAL

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2011-04-21 : 14:49:18

I have: tbl_UserDeposit
DepositID int
ApplicationId uniqueidentifier
UserId uniqueidentifier
TransactionAmount float
TransactionStatus bit
TransactionDate datetime
TransactionType int
CurrencyCode varchar(50)

tbl_UserWithdrawal:
WizhdrawalID int
ApplicationId uniqueidentifier
UserId uniqueidentifier
TransactionAmount float
TransactionStatus bit
TransactionDate datetime
TransactionType int
CurrencyCode varchar(50)

tbl_UserBonuses:
BonusID int
ApplicationId uniqueidentifier
UserId uniqueidentifier
TransactionAmount float
TransactionStatus bit
TransactionDate datetime
TransactionType int
CurrencyCode varchar(50)

tbl_RegisteredUser:
UserId uniqueidentifier
ApplicationId uniqueidentifier
IsMale bit
FirstName varchar(150)
Surname varchar(150)
Street varchar(150)
StreetNumber nvarchar(20)
ZipCode nvarchar(50)
City nvarchar(50)
Country nvarchar(50)
Region nvarchar(50)
Currency varchar(3)
DateOfBirth date
Telephone nvarchar(30)
Mobile nvarchar(30)
BonusCode nvarchar(30)
AffiliateId int
ChangeUserStatusNote nvarchar(500)
IsApprovedDocument bit

My balance: 0,00 €

I deposit 10,00 €.

My balance: 10,00 €

I again deposit 25,00 €

My balance: 35,00 €

Withdrawal: 8,00 €

My balance: 27,00 € - amount column in table tbl_registeredusers?

My question:
Is it necessary to make a new column "Amount" in the table tbl_registeredusers?

Okey, if the answer is no,
how I get balance the situation?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 15:31:46

No.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-21 : 15:47:15

quote:
Originally posted by robvolk

No.

Nice.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2011-04-21 : 16:18:23

quote:
Originally posted by programer

I have: tbl_UserDeposit
DepositID int
ApplicationId uniqueidentifier
UserId uniqueidentifier
TransactionAmount float
TransactionStatus bit
TransactionDate datetime
TransactionType int
CurrencyCode varchar(50)

tbl_UserWithdrawal:
WizhdrawalID int
ApplicationId uniqueidentifier
UserId uniqueidentifier
TransactionAmount float
TransactionStatus bit
TransactionDate datetime
TransactionType int
CurrencyCode varchar(50)

tbl_UserBonuses:
BonusID int
ApplicationId uniqueidentifier
UserId uniqueidentifier
TransactionAmount float
TransactionStatus bit
TransactionDate datetime
TransactionType int
CurrencyCode varchar(50)

tbl_RegisteredUser:
UserId uniqueidentifier
ApplicationId uniqueidentifier
IsMale bit
FirstName varchar(150)
Surname varchar(150)
Street varchar(150)
StreetNumber nvarchar(20)
ZipCode nvarchar(50)
City nvarchar(50)
Country nvarchar(50)
Region nvarchar(50)
Currency varchar(3)
DateOfBirth date
Telephone nvarchar(30)
Mobile nvarchar(30)
BonusCode nvarchar(30)
AffiliateId int
ChangeUserStatusNote nvarchar(500)
IsApprovedDocument bit

My balance: 0,00 €

I deposit 10,00 €.

My balance: 10,00 €

I again deposit 25,00 €

My balance: 35,00 €

Withdrawal: 8,00 €

My balance: 27,00 € - amount column in table tbl_registeredusers?

My question:
Is it necessary to make a new column "Amount" in the table tbl_registeredusers?

programer
Posting Yak Master

221 Posts

Posted - 2011-04-21 : 16:18:43

Okey, if the answer is no,
how I get balance the situation?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 16:35:37

Assuming you stick with the 3 tables:

SELECT SUM(TransAmount) Balance FROM 
(
	SELECT SUM(TransactionAmount) TransAmount FROM tbl_UserDeposit WHERE UserId='user id'
	UNION ALL
	SELECT SUM(TransactionAmount) TransAmount FROM tbl_UserWithdrawal WHERE UserId='user id'
	UNION ALL
	SELECT SUM(TransactionAmount) TransAmount FROM tbl_UserBonuses WHERE UserId='user id'
) All_Sums

I'm assuming the withdrawals table stores the TransactionAmount as a negative number (and it better be) otherwise you'll have to fix the query. You're better off combining the 3 tables into 1, and maybe add a Type column for Deposit, Withdrawal, or Bonus. You've already got a TransactionType column so that should suffice.

And if you're actually going to store monetary amounts in this, stop using float or real. Use money, or if you have to calculate interest or similar percentages, use decimal.

programer
Posting Yak Master

221 Posts

Posted - 2011-04-21 : 16:49:58

Thank you very much for your answer.

Only this makes me interested in:

This the tables are given as an example.

Unable to use only one table, if I have an additional table tbl_slip:
ApplicationId uniqueidentifier
UserId uniqueidentifier
Amount float
dateslip datetime
infoslip varchar (255)

I have several tables with different data and therefore it is impossible to put everything in one table.

Is it feasible?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 17:03:43

[code]SELECT SUM(TransAmount) Balance FROM
(
SELECT SUM(TransactionAmount) TransAmount FROM tbl_UserDeposit WHERE UserId='user id'
UNION ALL
SELECT SUM(TransactionAmount) TransAmount FROM tbl_UserWithdrawal WHERE UserId='user id'
UNION ALL
SELECT SUM(TransactionAmount) TransAmount FROM tbl_UserBonuses WHERE UserId='user id'
UNION ALL
SELECT SUM(Amount) TransAmount FROM tbl_slip WHERE UserId='user id'
) All_Sums[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources