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.
| Author |
Topic |
|
programer
Posting Yak Master
221 Posts |
 Posted - 2011-07-25 : 17:06:43
|
| Hi,my question is:i have a member that can pay in 3 different way credit card check transfer from bank accounthow design the table that contain the history of payment.the fields of every way is different .if i save all in one table , i have lot of blank field ?what the right way to deal it ? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-25 : 20:31:31
|
You could add a payment type column that would indicate which type of payment was received, for example:create table dbo.PaymentHistory( account_id int not null, payment_id int not null, payment_type varchar(32) check (payment_type in ('credit_card','check','transfer')), payment_amount decimal(19,2) not null primary key clustered (account_id asc,payment_id asc))I am only showing a skeleton, you will probably need additional columns such as payment date etc. Also, whether the primary key should be the composite key as I did, or something else etc. would depend on your business needs.For fields that are not common, you can have 3 separate tables for each type of payment, with a foreign key into this table. What I am thinking is something along the lines of the example described in this problem (even though the physical problem itself is very different) http://quoderat.megginson.com/2010/09/25/sql-and-simple-polymorphism/ |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-25 : 23:21:29
|
I'd add a PaymentType table instead of a check()create table dbo.PaymentHistory( account_id int not null, payment_id int not null, payment_type_id int not null, -- Foreign Key to PaymentType table payment_amount decimal(19,2) not null primary key clustered (account_id asc,payment_id asc)) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-26 : 07:32:35
|
quote:
Originally posted by russell
I'd add a PaymentType table instead of a check()create table dbo.PaymentHistory( account_id int not null, payment_id int not null, payment_type_id int not null, -- Foreign Key to PaymentType table payment_amount decimal(19,2) not null primary key clustered (account_id asc,payment_id asc))
Yes! A column with repeated character values (such as 'credit_card'), in addition to taking up more space, being hard to internationalize etc., is also unsightly and ugly, and may even be violating some of the rules and regulations written by Mr. Codd. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-26 : 09:57:31
|
lol sunita.  |
|
|
|
|
|
|
|
|
save different details of payment (credit or...)