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