![](/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) |
"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) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
On Fri, 10 Oct 2008 00:35:15 -0700, Darren New <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Kyle wrote:
> On Fri, 10 Oct 2008 00:35:15 -0700, Darren New <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |