NYCPHP Meetup

[Tim Lieberman]

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.

What's wrong with that?  I think you've got it right -- it all comes  
down to the domain you're modeling.

>
> 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 ;)

True.  I don't think a hard and fast rule is appropriate here.


>
>> 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
>

That's pretty much what I'd actually do in practice.  In every case I  
can think of, the trailing "_id" is enough to indicate that this is a  
foreign key.


> 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?

Right.  Like I said, the underscore doesn't have any special semantic  
meaning, unless the following two characters are an "i" and then a "d"

> And if you did the latter, would you do:
>
> service_definition__id at that point?  or service__definition__id?

Were I doing things that way, I'd do the former.

But it's just a thought.  I've never done things that way, and it  
seems confusing -- the visual difference between _ and __ is too small.

-Tim