![](/i/fill.gif) |
![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
"Darren New" <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
"Darren New" <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
"Darren New" <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |