Say you're doing some accounting stuff, and you have different kinds of 
accounts, but you want a table of movements of money between the 
accounts. I'm trying to figure out how to get the details of having a 
single record split over one of any of several different tables to be 
enforced by constraints.

For example, I have a savings account type, a checking account time, a 
credit card account type, etc. Obviously the credit card account is 
going to have a CC#, an expiration date, etc. The savings account might 
have some other paramters like guaranteed interest rate or something. So 
obviously they can't all be in the same table.

Here, I'm trying to figure out how you would set up the parent account 
such that you can't have a parent account with an account type of 
"savings" without having a corresponding entry in the savings account. 
Are triggers the only way to ensure this? (I mean, other than obviously 
writing the insert statements correctly. :-) A simple foreign key 
restraint keeps it going in the other direction.

So you'd put one table in with the account ID and anything that's common 
to all accounts (owner, creation date, etc) and other tables, one for 
each kind of account. And then anything that can reference multiple 
kinds of accounts (like a transfer of money) would have a FK into the 
account table.

// Using awful syntax because I'm too lazy to look it up...
create table account (
   id autoinc integer,
   acctnumber varchar(50),
   accttype enum (savings, checking, ...),
   owner integer
   primary key id
   owner foreign key customers.id
);
create table savings (
   id integer,
   interestrate decimal,
   foreign key id is account.id
);
create table creditcard (
   id integer,
   exxpirationdate date,
   foreign key id is account.id
);

So all savings accounts have details in the savings table, with a 
foreign key into the master account table. But what keeps a record in 
the account table from losing its corresponding record in the savings table?

Thanks for thoughts. :-)

-- 
Darren New / San Diego, CA, USA (PST)

Post a reply to this message