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
