"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

Kyle wrote:
> 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...

Likely correct. This doesn't really help too much if you don't have 
check-constraints tho. Having them in separate tables has the benefit 
that you don't need to store the empties for fields that aren't 
applicable, and you can add a new type of account without having to frob 
the big table. Of course, if it's easy to enforce data consistency one 
way and not the other, that pretty much trumps things. :-)

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

Post a reply to this message

On Fri, 10 Oct 2008 00:35:15 -0700, Darren New <dne### [at] sanrrcom> wrote:

>Likely correct. This doesn't really help too much if you don't have 
>check-constraints tho. 

You would still want a check constraint, driven by the account type.

>Having them in separate tables has the benefit 
>that you don't need to store the empties for fields that aren't 
>applicable, 

Does MySql use much space for null values?  Either way, that's really a non-issue,
compared to the benefit of data integrity.

>and you can add a new type of account without having to frob 
>the big table. 

It boils down to "alter big-table" vs. "create new-table".  With "create new-table",
you'd additionally have to set up foreign key constraints back to the big table's
primary key.  You'd have to
modify the check constraint in either case.

Post a reply to this message

Kyle wrote:
> On Fri, 10 Oct 2008 00:35:15 -0700, Darren New <dne### [at] sanrrcom> wrote:
> 
>> Likely correct. This doesn't really help too much if you don't have 
>> check-constraints tho. 
> 
> You would still want a check constraint, driven by the account type.

Sure. MySql doesn't support them, tho.  Definitely gonna use postgresql 
for the next project I get to start from scratch. :-)

> Does MySql use much space for null values?  Either way, that's really a non-issue,
compared to the benefit of data integrity.

I think I said that. :-)

>> and you can add a new type of account without having to frob 
>> the big table. 
> 
> It boils down to "alter big-table" vs. "create new-table".  With "create new-table",
you'd additionally have to set up foreign key constraints back to the big table's
primary key.  You'd have to
> modify the check constraint in either case.

Sure. With "create new-table", you don't have to worry about whether you 
broke any part of the program that doesn't deal with the new types of 
accounts, at least.  But it's really half of one, a dozen of the other, 
I'll agree. :-)

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

Post a reply to this message