NYCPHP Meetup

[Matt Juszczak]

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