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

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:48eb04fa$1@news.povray.org...
> 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.

You'd probably need triggers. It's hard to enforce a 1-1 relationship 
without.

Depending how many columns are only applicable to one account type, I might 
take a short cut and put everything in one table. It does depend though.

Post a reply to this message

Gail wrote:
> You'd probably need triggers. It's hard to enforce a 1-1 relationship 
> without.

Thanks!  I just wasn't sure if there was something clever I was missing.

> Depending how many columns are only applicable to one account type, I 
> might take a short cut and put everything in one table. It does depend 
> though.

That doesn't really solve the consistency problem, tho. You could still 
wind up with an account where all the details are null, for example. I 
think SQL is just a bit too lacking in its data definition language to 
enforce some of the more complex constraints (without triggers, at least).

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

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:48ec5d23$1@news.povray.org...
> Gail wrote:

>> Depending how many columns are only applicable to one account type, I 
>> might take a short cut and put everything in one table. It does depend 
>> though.
>
> That doesn't really solve the consistency problem, tho. You could still 
> wind up with an account where all the details are null, for example.

For that you could use check constraints.

ALTER TABLE Accounts ADD CONSTRAINT ck_AccountType CHECK (AccountType = 
'CreditCard' AND exxpirationdate IS NOT NULL)
etc

Post a reply to this message

Gail wrote:
> ALTER TABLE Accounts ADD CONSTRAINT ck_AccountType CHECK (AccountType = 
> 'CreditCard' AND exxpirationdate IS NOT NULL)

Ah. Very cool.  Sadly, this is MySQL, which sucks, so altho it parses 
check constraints, it doesn't actually enforce them.  Welcome to the 
wonderful world of toy RDBMs.

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

Post a reply to this message

Darren New wrote:
> Gail wrote:
>> ALTER TABLE Accounts ADD CONSTRAINT ck_AccountType CHECK (AccountType =
>> 'CreditCard' AND exxpirationdate IS NOT NULL)
> 
> Ah. Very cool.  Sadly, this is MySQL, which sucks, so altho it parses
> check constraints, it doesn't actually enforce them.

Not even in InnoDB?

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:48ec835d$1@news.povray.org...
> Gail wrote:
>> ALTER TABLE Accounts ADD CONSTRAINT ck_AccountType CHECK (AccountType = 
>> 'CreditCard' AND exxpirationdate IS NOT NULL)
>
> Ah. Very cool.  Sadly, this is MySQL, which sucks, so altho it parses 
> check constraints, it doesn't actually enforce them.  Welcome to the 
> wonderful world of toy RDBMs.

Oh lovely. Don't suppose you can port it to SQL Server? Express edition's 
free.

Does MySQL enforce foreign keys yet?

Post a reply to this message

Nicolas Alvarez wrote:
> Not even in InnoDB?

Foreign keys, yes. Check constraints, no. At least not in 5.0 docs.

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

Post a reply to this message

Gail wrote:
> Oh lovely. Don't suppose you can port it to SQL Server? Express 
> edition's free.

No. I definitely think in the future I'll pick postgresql for Linux 
production DBs.

> Does MySQL enforce foreign keys yet?

Only sometimes.  That is, there are different back-end storage 
mechanisms, and only one actually enforces FKs and transactions. 
Otherwise you're pretty screwed.

Maybe the bleeding edge allows it, but I haven't seen it.

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

Post a reply to this message

I think you're really asking for trouble splitting this info up into separate tables,
since it is all account id specific.  You WILL get inconsistent data at some point. 
I'd suggest to just suck it
up and put all of the columns into one table, like this...

create table account (
   id autoinc integer,
   acctnumber varchar(50),
   accttype enum (savings, checking, ...),
   owner integer,
// start of account type specific columns
   interestrate decimal,
   exxpirationdate date,
// end of account type specific columns
   primary key id
   owner foreign key customers.id
);

Post a reply to this message