quote:

---

Originally posted by stephe40

I'm just curious what normalization rule is being violated then?

- Eric

---

--------------------------------------------------------------
SCHEMA1 (Which normal forms does it break and Why?)
--------------------------------------------------------------
R0(TransactionType)
	PK(TransactionType)

R(Key,TransactionType,Date,Amount,ccNum,ccExp,checkNum)
	PRIMARY KEY(Key)
	FOREIGN KEY(TransactionType) REFERENCES R0
	Key -> TransactionType
	Key -> Date
	Key -> Amount
	{Key,TransactionType} -> ccNum
	{Key,TransactionType} -> ccExp
	{Key,TransactionType} -> checkNum

--------------------------------------------------------------
SCHEMA2 (R is projected into R1+R2+R3) (Is this 3NF?, are there other better ways?)
--------------------------------------------------------------
R0(TransactionType)
	PK(TransactionType)

R1(Key,Date,Amount)
	PRIMARY KEY(Key)
	Key -> Date
	Key -> Amount
	
R2(Key,TransactionType,ccNum,ccExp)
	PRIMARY KEY(Key)
	FOREIGN KEY(Key) REFERENCES R1
	FOREIGN KEY(TransactionType) REFERENCES R0
	CHECK(TransactionType = 'CreditCard')
	Key -> TransactionType
	Key -> ccNum
	Key -> ccExp

R3(Key,TransactionType,checkNum)
	PRIMARY KEY(Key)
	FOREIGN KEY(Key) REFERENCES R1
	FOREIGN KEY(TransactionType) REFERENCES R0
	CHECK(TransactionType = 'Check')
	Key -> TransactionType
	Key -> checkNum

quote:

---

"http://en.wikipedia.org/wiki/Database_normalization"
Third normal form
Third normal form (or 3NF) requires that there are no non-trivial functional dependencies of non-key attributes on something other than a superset of a candidate key.
A relation is in 3NF if none of the non-Primary Key attributes are a fact about any other non-Primary Key attribute.
Another way of saying this is that all non-key attributes are mutually independent (i.e. there should not be transitive dependencies).

---

quote:

---

"Introduction to Databse Systems" 6th ed. C.J. Date
Third Normal Form (even more formal definition): A relation is in third normal
form(3NF) if and only of, for all time, each tuple consists of a primary key value
that identifies some entity, together with a set of zero or more mutually indepen-
dent attribute values that describe that entity in some way.

Third Normal Form (definition assuming only one candidate key, which is thus
the primary key): A relation is in 3NF if and only if it is in 2NF and every nonkey
attribute is nontransitively dependent on the primary key. ("No transitive depend-
encies" implies no mutual dependencies, in the sense of that term explained at the
beginning of this section.)

• Two or more attributes are mutually independent if none of them is functionally
  dependent on any combination of the others. Such independence implies that each
  such attribute can be updated independently of all the rest.

---

quote:

---

"Fundamentals of Database Systems" 4th ed. Elmasri,Navathe
Definition. A relation schema R is in third normal form (3NF) if, whenever a
nontrivial functional dependency X->A holds in R, either
(a) X is a superkey of R, or
(b) A is a prime attribute of R.

---

Transaction (Key, Date, Amount)
CreditCardTransaction(Key, ccNum,ccExp)
ChequeTransaction(Key, checkNum)

quote:

---

Originally posted by rockmoose
.....check(either {ccNum,ccExp} or checkNum filled in)

---

CREATE TABLE [dbo].[guests] (
	[guestID] [int] IDENTITY (1000, 1) NOT NULL ,
	[firstName] [varchar] (100) ,
	[lastName] [varchar] (100) ,
	[middleName] [varchar] (50) ,
	[address1] [varchar] (200) ,
	[address2] [varchar] (200) 
	(most columns removed to shorten this up)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[reservations] (
	[resID] [int] IDENTITY (1, 1) NOT NULL ,
	[notes] [varchar] (5000) ,
	[guestID] [int] NOT NULL ,
	[yearID] [int] NOT NULL ,
	[pending] [bit] NOT NULL ,
	[dateAdded] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transactions] (
	[transID] [int] IDENTITY (1, 1) NOT NULL ,
	[payTypeID] [int] NOT NULL ,
	[resID] [int] NOT NULL ,
	[amount] [numeric](18, 2) NOT NULL ,
	[numbers] [varchar] (50) ,
	[ccExp] [char] (4) ,
	[transDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[paymentTypes] (
	[payTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[payTypeDesc] [varchar] (50) 
) ON [PRIMARY]
GO

-- PKs....... --

ALTER TABLE [dbo].[guests] ADD 
	CONSTRAINT [PK_guests] PRIMARY KEY  CLUSTERED 
	(
		[guestID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[reservations] ADD 
	CONSTRAINT [PK_reservations] PRIMARY KEY  CLUSTERED 
	(
		[resID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[transactions] ADD 
	CONSTRAINT [PK_transactions] PRIMARY KEY  CLUSTERED 
	(
		[transID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[paymentTypes] ADD 
	CONSTRAINT [PK_paymentTypes] PRIMARY KEY  CLUSTERED 
	(
		[payTypeID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO


-- Check Constraints .......... --


ALTER TABLE [dbo].[reservations] ADD 

	CONSTRAINT [FK_reservations_guests] FOREIGN KEY 
	(
		[guestID]
	) REFERENCES [dbo].[guests] (
		[guestID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[transactions] ADD 
	CONSTRAINT [FK_transactions_paymentTypes] FOREIGN KEY 
	(
		[payTypeID]
	) REFERENCES [dbo].[paymentTypes] (
		[payTypeID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_transactions_reservations] FOREIGN KEY 
	(
		[resID]
	) REFERENCES [dbo].[reservations] (
		[resID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

quote:

---

Originally posted by byrmol


Just use a big string field and stuff every thing into it...credit card numbers, expiry date, cheque, pay pal.. anything you want... I am sure the front end will love that.. :-)

Will the front end have to change when a new payment type is added? If it does then the maintenance side of the argument is mute..

How many times a day does this report get run? Once? This is the last thing you should be worried about at this stage.. Concentrate on the data and making it bulletproof to mistakes..

I am yet to come across a client who willing sacrifices a "slow" truth for a "fast" lie.

Whatever design you choose, I STRONGLY recommend using views for (at the very least) data integrity of the different payment types.

DavidM

A front-end is something that tries to violate a back-end.

---

paymentTypes (payTypeID int, payTypeDescrip varchar(50))
transactions (transID int, payTypeID int, transDate, datetime, amount money)
extendedPaymentProperties (payPropID int, payPropName varchar(50))
extendedPaymentPropertyValues (transID int, payPropID int, value varchar(50))

extendedPaymentProperties 
payPropID	payPropName
1		Credit Card Number
2		Credit Card Exp
3		Check Number
4		PayPal Confirmation Number
5		Some other useless number

paymentTypes
payTypeID	payTypeDesc
1		Credit
2		Check
3		Cash
4		Paypal

transactions
transID		payTypeID	transDate		amount
1001		1		1/1/05 1:00 AM	50.34
1002		4		1/1/05 1:11 AM	123.13
1003		2		1/1/05 1:34 AM	200.00
1004		3		1/1/05 1:35 AM	123.34

transID		payPropID	value
1001		1		1234123412341234
1001		2		06/08
1002		4		123AUD28D902KL
1003		3		4321

create schema authorization dbo
create table paymentTypes (payTypeID varchar(15) primary key
	, payTypeDesc varchar(255) not null default(''))
create table transactionBase (transID INT IDENTITY(1,1) primary key check(transid > 0)
	, payTypeID varchar(15) not null, amount money not null check(amount > 0)
	, transDate datetime not null default(getdate())
	, foreign key (payTypeID) references paymentTypes (payTypeID))
create table transactionCreditCard(transID INT primary key default(IDENT_CURRENT('transactionBase'))
	, ccNumber varchar(15)not null check(len(ccNumber) > 0)	, ccExp datetime not null
	, foreign key (transID) references transactionBase (transID))
create table transactionCheck (transID INT primary key default(IDENT_CURRENT('transactionBase'))
	, checkNum varchar(35) not null
	, foreign key (transID) references transactionBase (transID))

create view transaction_Cash
AS
select transID, payTypeID, amount, transDate
from dbo.transactionbase
WHERE payTypeID = 'Cash'
with check option

create view transaction_Cheque_CONSTRAINT
AS
select tb.transID, tb.payTypeID, tb.amount, tb.transDate
from dbo.transactionbase tb
WHERE payTypeID = 'Cheque'
with check option

create view transaction_CreditCard_CONSTRAINT
AS
select tb.transID, tb.payTypeID, tb.amount, tb.transDate
from dbo.transactionbase tb
WHERE payTypeID = 'Credit Card'
with check option

create view transaction_Cheque
AS
select tb.transID, tb.payTypeID, tb.amount, tb.transDate, tc.checkNum
from dbo.transactionbase tb
inner join dbo.transactionCheck tc on tc.transID = tb.transID
WHERE payTypeID = 'Cheque'

create view transaction_CreditCard
AS
select tb.transID, tb.payTypeID, tb.amount, tb.transDate, tc.ccNumber, tc.ccExp
from dbo.transactionBase tb
inner join dbo.transactionCreditCard tc on tc.transID = tb.transID
WHERE tb.payTypeID = 'Credit Card'

create trigger trg_transaction_Cheque_INSERT
on transaction_Cheque
instead of insert
as
insert dbo.transaction_Cheque_CONSTRAINT ( payTypeID, amount, transDate)
	select paytypeid, amount, transdate
	from inserted i
insert dbo.transactionCheck (checkNum)
	select checkNum
	from inserted i
go
create trigger trg_transaction_CreditCard_INSERT
on transaction_CreditCard
instead of insert
as

insert dbo.transaction_CreditCard_CONSTRAINT ( payTypeID, amount, transDate)
	select paytypeid, amount, transdate
	from inserted i
insert dbo.transactionCreditCard (  ccNumber, ccExp)
	select ccNumber, ccExp
	from inserted i
go

insert paymentTypes (payTypeID, payTypeDesc)
	select 'Cash', ''
	union all
	select 'Cheque', ''
	union all
	select 'Credit Card', ''
go
insert dbo.transaction_cash (payTypeID, amount, transDate)
	values ('Cash', RAND() * 1000, getdate())
insert dbo.transaction_Cheque (transID, payTypeID, amount, transDate, checkNum)
	values (0, 'Cheque', RAND() * 1000, getdate(), 'd2345345345')
insert dbo.transaction_CreditCard (transID, payTypeID, amount, transDate, ccNumber, ccExp)
	values (0, 'Credit Card', RAND() * 1000, getdate(), '123213', '01/05/2002')

create view transaction_detailed
as
select transID, payTypeID, amount, transDate, 'n/a' as details
from dbo.transaction_Cash
union all
select transID, payTypeID, amount, transDate, checkNum 
from dbo.transaction_Cheque
union all
select transID, payTypeID, amount, transDate, ccNumber + '(' + CAST(ccExp as varchar(20)) + ')' as details
from dbo.transaction_CreditCard
go
select * from  transaction_cash
select * from  transaction_cheque
select * from  transaction_CreditCard
select * from  transaction_detailed