Structure database - SQL Server Forums

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

Structure database

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2011-12-25 : 07:23:11

Hello,

What do you suggest for my problem:

I have two payment methods.

Moneybookers:
- E-mail

Neteller:
- Account-ID
- Secure ID

For data storage use two different tables, or one?

for example. tbl_PaymentMethodInfo
Id, AttributeName, Value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-25 : 07:27:33

in future if there are chances of new payment methods to be added, i would suggest two tables like

tbl_PaymentInfo
Id, PaymentTypeId, Value

and tbl_PaymentType
Id,PaymentType

the PaymentTypeId in tbl_PaymentInfo will be linked to Id field of tbl_PaymentType

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2011-12-25 : 07:36:47

quote:
Originally posted by visakh16

in future if there are chances of new payment methods to be added, i would suggest two tables like

tbl_PaymentInfo
Id, PaymentTypeId, Value

and tbl_PaymentType
Id,PaymentType

the PaymentTypeId in tbl_PaymentInfo will be linked to Id field of tbl_PaymentType

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

first table:
tbl_PaymentType
Id,PaymentType
1, Moneybookers
2, Neteller,
3, Bank transfer

tbl_PaymentInfo
Id, PaymentTypeId, Value
1, 1 email@email
2, 2 secure id
3, 2 account id

The second table is missing an additional column "AttributeName" providing as follows:

tbl_PaymentInfo
Id, PaymentTypeId,AttributeName, Value
1, 1 email mb email@email
2, 2 secure nt 15055
3, 2 account id nt 295554
4, 3 bank account 054564646465
4, 3 SWIFT BSMSM

AttributeName must be, because I know what the variable belongs.
Is this correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-25 : 23:18:44

whats the need of attribute name here? Its just short form of data in PaymentType isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2011-12-26 : 06:57:09

quote:
Originally posted by visakh16

whats the need of attribute name here? Its just short form of data in PaymentType isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hello,

In which of column to write: SWIFT, IBAN, SECURE ID, ACCOUNT IT, if have not column AttributeName?
Can you explain more this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 07:01:21

is that another attribute that has to be linked to paymenttype? if yes, you can have that field in the paymenttype table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2011-12-26 : 07:22:59

quote:
Originally posted by visakh16

is that another attribute that has to be linked to paymenttype? if yes, you can have that field in the paymenttype table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In one of these tables I have to save the IBAN word ... We are talking about the word "IBAN".

Like this:
AttributeName, Value
IBAN, 4728347728374
SWIFT, KBMKKK

If you do not use the AttributeName how to find data IBAN?

programer
Posting Yak Master

221 Posts

Posted - 2011-12-26 : 08:32:21

quote:
Originally posted by programer

quote:
Originally posted by visakh16

is that another attribute that has to be linked to paymenttype? if yes, you can have that field in the paymenttype table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In one of these tables I have to save the IBAN word ... We are talking about the word "IBAN".

Like this:
AttributeName, Value
IBAN, 4728347728374
SWIFT, KBMKKK

If you do not use the AttributeName how to find data IBAN?

Is it a big problem if I write every word in the AttributeName:
UserId, AttributeName
736, SWIFT
847, SWIFT

So for each user, it is necessary to save the word "SWIFT" in the AttributeName column?
Is that ok? What is the occupancy of space of the database?

programer
Posting Yak Master

221 Posts

Posted - 2011-12-26 : 09:04:38

quote:
Originally posted by programer

quote:
Originally posted by programer

quote:
Originally posted by visakh16

is that another attribute that has to be linked to paymenttype? if yes, you can have that field in the paymenttype table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In one of these tables I have to save the IBAN word ... We are talking about the word "IBAN".

Like this:
AttributeName, Value
IBAN, 4728347728374
SWIFT, KBMKKK

If you do not use the AttributeName how to find data IBAN?

Is it a big problem if I write every word in the AttributeName:
UserId, AttributeName
736, SWIFT
847, SWIFT

So for each user, it is necessary to save the word "SWIFT" in the AttributeName column?
Is that ok? What is the occupancy of space of the database?

Hello,

I have more payment method, like: paypal, moneybookers, neteller, bank transfer.

I want to use two tables.

tbl_PaymentType:
Id, PaymentType
1, Moneybookers
2, Neteller
3, PayPal
4, Bank transfer

tbl_PaymentDetails:
Id,UserId, PaymentTypeId, AttributeName, Value
1,231,1, Email_mb, moneybookers_email@moneybookers.com
2,231,2, Email_nt, neteller_email@neteller.com
3,231,3, Email_paypal, paypal_email@paypal.com
4,231,4, SWIFT, KBMSJEWW
5,231,4, IBAN, ATR7737346767767
6,231,4, NAME OF BANK, name bank
7,231,4, NAME OF OWNER, owner
8,350,4, SWIFT, ZWUZWUI
9,350,4, IBAN, PTR439898
10,350,4, NAME OF BANK, name bank2
11,350,4, NAME OF OWNER, owner2

the_PaymentTypeId in tbl_PaymentDetails will be linked to Id field of tbl_PaymentType.

I have a few questions:

1. In the table tbl_PaymentType I saved type of payment system and in future (if will it neccessary) to add manually of new payment system?
2. Is my second table correctly structured? Whenever a bank account is saved, the table also recorded "SWIFT" ?

Please check my structure table.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 12:19:27

1. yep. you can add as many paymenttypes as you want in it in future
2. I still feel AttributeName should be in tbl_PaymentType as its linked with payment type.
for saving SWIFT details automatically on bank account save, you need to include logic in your insert procedure or include a trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2011-12-26 : 12:36:25

quote:
Originally posted by visakh16

1. yep. you can add as many paymenttypes as you want in it in future
2. I still feel AttributeName should be in tbl_PaymentType as its linked with payment type.
for saving SWIFT details automatically on bank account save, you need to include logic in your insert procedure or include a trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I understand now.
I can not understand are how in my case data grouped.

If one user has multiple accounts moneybookers, so I have to display correctly:
test (moneybookers@moneybookers.com)
test2 (moneybookers2@moneybookers.com)

But in my case I can not because I do not have identical ID.

There is a way that in the first table is stored for each payment, I save a new row like this:
tbl_PaymentType:
Id PaymentType
1, Moneybookers
2, Moneybookers
3, Neteller
4, Bank transfer
5, Moneybookers
6, Neteller

So in this way I have a unique Id in Table PaymentDetails:
1,231,1, Email_mb, moneybookers_email@moneybookers.com
1,231,1, Email_mb, moneybookers_email2@moneybookers.com

It is also necessary to do it if I want to get information from the bank transfer.

Is this correct?

In the first table tbl_PaymentType can if necessary add new column "IsBlocked", "IsDeleted", "IsDeposit".

In this case this table also apply to deposits and withdrawals. Is correct?

Thanks again for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 02:57:38

ok. in that case it might be worth adding the email etc attributes associated to PaymentTypes in another table as its one to many. you can add PaymentTyepId,UserId,AttributeId as composite key. In case its not directly related to paymenttype but only to user you can dispense with PaymentTypeId reference.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources