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
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/show_participation.php
