NYCPHP Meetup

[Russ Demarest]

Matt,

	It is really hard to talk about tables without understanding the data  
needs. If you want to come up with an example of what you need to do  
we could all suggest structures. I don't really get the levels concept  
of a relational db, but what do I know :)

	Part of good db design is to plan way into the future. Your customers  
may only have one account now, but is it possible in the future they  
could have 2? These are HUGE decisions that can come back with really  
big teeth.

	What is a service?

	Russ

On Sep 12, 2009, at 9:45 PM, Matt Juszczak wrote:

>> For lookup tables like an "account type", I'd certainly call the  
>> table "account_type", and not just "type".  Eventually you'll have  
>> an "order type" to deal with, so ... yeah.
>>
>> In the larger picture, you want to maintain enough specificity to  
>> keep things from getting confusing.  This is largely a function of  
>> the domain.  However, domains tend to grow, so it's better to err  
>> slightly on the side of verbose specificity.  For example, it's  
>> probably not a terrible idea to use "customer_account" instead of  
>> just "account", in case 12 months from now you need two new kinds  
>> of ".+_account"s
>
> But account is the "top level".  Every customer has an account.   
> Every account has a type.  So really, the top level is account type,  
> because that's the only table out of the three that has no "parent"  
> of it's own. But why would I create a customer and a  
> customer_account table?  Sure, each customer has one and only one  
> account, so it makes sense, just like each account has one and only  
> one type.   But:
>
> account -> account type
> customer -> account
>
> aren't the same "sort of relationship" to me, even though they are  
> both many to one relationships.  Tying the account table to customer  
> at this point (customer_account) would be bad, because every service  
> in the "service" table has one and only one account as well - so why  
> wouldn't we call it service_account and service?  The same sort of  
> thing.  So at that point, I would probably do:
>
> account
> account_type
> customer
> service
> service_definition
> service_type
> invoice
> invoice_type
>
> but at that point, there really is no standard.  I sort of just  
> picked "meaningful" top level tables.
>
> Bah, it's all confusing to me :) I guess there really is no way to  
> do it. If there are 10 levels of one:many relationships, you can't  
> underscore them all out.
>
> one
> one_two
> one_two_three
> one_two_three_four
>
> would get quite confusing ;)
>
>
>> I tend to avoid that, as underscores can be really useful to keep  
>> things legible.  lowerCamelCase, to me, is just kind of ugly in  
>> myslql and other rdbmses where identifiers are case-insensitive.
>>
>> If you really want, I suppose you could use a standard where a  
>> double underscore indicates some foreign key: account__id  
>> REFERENCES account.id
>
> OK.
>
> So you would do something like:
>
> first_name
> account_id
> last_name
> service_definition_id
>
> Stuff like that?  Even though first_name is just a field (and  
> last_name), while account_id is the id column in the account table  
> and service_definition_id is the id column in the service_definition  
> table?
>
> And if you did the latter, would you do:
>
> service_definition__id at that point?  or service__definition__id?
>
> Thanks!
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/show_participation.php