Database, table, and column naming schemes

[Matt Juszczak]

Does anyone have any good naming conventions for mysql databases and tables and columns?  I'm developing a complex lamp project now, and my normal convention doesn't seem to want to work too well for this project - there are a few conflicts.

_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Tim Lieberman-2]

In my experience, the most important thing is consistency.  Almost  
everything else is a matter of taste.

For instance, some folks like to name columns with the table name as a  
prefix on every column (except foreign keys):

create table a(
        a_id int,
        a_value varchar(64),
        a_created datetime
);

create table b (
        b_id int,
        a_id int references a.a_id
        b_value varchar(128),
        b_created datetime
);

This is a smart thing to do as every column, across all tables, has a  
unique name (unless it's a foreign key)

However, it can create a lot of typing, which can be annoying.

On the other hand, you can be more concise:

create table a(
        id int,
        value varchar(64)
        created datetime
);

create table b(
        id int,
        value varchar(64),
        created datetime
);

This saves some typing, but can create annoying ambiguity.  Join  
operations end up requiring more specific selection critera (SELECT  
a.title as a_title, b.title as b_title).

I used to use the former method almost exclusively.  However, as I  
started playing with various frameworks, I've switched to the latter  
as those I've worked with kind of expect it.  Probably because various  
_call() based magic ends up looking nicer in userland code.

If you have more specific considerations, feel free to get more  
specific.

-Tim




On Sep 12, 2009, at 8:17 PM, matt@... wrote:

> Does anyone have any good naming conventions for mysql databases and  
> tables and columns?  I'm developing a complex lamp project now, and  
> my normal convention doesn't seem to want to work too well for this  
> project - there are a few conflicts.
>
> _______________________________________________
> 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

[Matt Juszczak]

> I used to use the former method almost exclusively.  However, as I started
> playing with various frameworks, I've switched to the latter as those I've
> worked with kind of expect it.  Probably because various _call() based magic
> ends up looking nicer in userland code.
>
> If you have more specific considerations, feel free to get more specific.

Hi Tim,

Actually, you probably just covered about 99% of what I've read today,
which is both a good and a bad thing - first, it's good because I know
that information is out dated, but bad because I still have a few
questions!  But I'll try to clear my questions up.

I've always used the second method you recommend.  It works well for a
schema like this:

CREATE TABLE `country` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `name` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `state` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `county_id` int(11) unsigned NOT NULL,
   `name` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `city` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `state_id` int(11) unsigned NOT NULL,
   `name` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

each city has a state_id, and each state, has a country_id.  And since
cities, states, and countries all are sort of self explanatory, the schema
sort of documents itself.  But what if you're developing a billing system?
That's where it's hard, because things are different:

For instance:

CREATE TABLE `account` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `type_id` int(11) unsigned NOT NULL,
   `companyName` varchar(25) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `account_type` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `type` varchar(255) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `customer` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `account_id` int(11) unsigned NOT NULL,
   `firstname` varchar(25) NOT NULL,
   `lastname` varchar(25) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

As you can see, each customer as an account_id.  But each account also has
type_id.  I chose to do this like I did above, but why didn't I call the
tables:

customer
customer_account
customer_account_type

After all, that would have worked the same way, right?  So I never really
know where to put the top level.  Is it just up to the person?  My guess
is, does it just only matter if the table names aren't self explanatory?
For instance, if I just had a table called "type", that isn't really
verbose, which is why I put account_ in front of it.

Also, as you can see, I only tend to use underscores when I'm referencing
a column in another table.  type_id for instance is account_type.id.
Which is why I did "firstname" or "companyName" (I did this on purpose to
show two possibilities).  Which one is the better one to use?
first_name/last_name/company_name or firstName, lastName, companyName?
The problem is, if I use first_name, but I'm also using type_id, then some
people might think that first_name references a column in the first (or a
similarly named) table called "name".

Thanks for your help!

-Matt

_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Tim Lieberman-2]

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

>> I used to use the former method almost exclusively.  However, as I  
>> started playing with various frameworks, I've switched to the  
>> latter as those I've worked with kind of expect it.  Probably  
>> because various _call() based magic ends up looking nicer in  
>> userland code.
>>
>> If you have more specific considerations, feel free to get more  
>> specific.
>
> Hi Tim,
>
> Actually, you probably just covered about 99% of what I've read  
> today, which is both a good and a bad thing - first, it's good  
> because I know that information is out dated, but bad because I  
> still have a few questions!  But I'll try to clear my questions up.

[snip]

>
> As you can see, each customer as an account_id.  But each account  
> also has type_id.  I chose to do this like I did above, but why  
> didn't I call the tables:
>
> customer
> customer_account
> customer_account_type
>
> After all, that would have worked the same way, right?  So I never  
> really know where to put the top level.  Is it just up to the  
> person?  My guess is, does it just only matter if the table names  
> aren't self explanatory? For instance, if I just had a table called  
> "type", that isn't really verbose, which is why I put account_ in  
> front of it.

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


> Also, as you can see, I only tend to use underscores when I'm  
> referencing a column in another table.  type_id for instance is  
> account_type.id. Which is why I did "firstname" or "companyName" (I  
> did this on purpose to show two possibilities).  Which one is the  
> better one to use? first_name/last_name/company_name or firstName,  
> lastName, companyName? The problem is, if I use first_name, but I'm  
> also using type_id, then some people might think that first_name  
> references a column in the first (or a similarly named) table called  
> "name".

For column names, it's tempting to give a specific meaning to an  
underscore, like you do.

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




_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Russ Demarest]

Matt,

This is how I originally learned it way back when and still believe.
SQL should read almost like english.

Tables are named in the plural.
Columns are singular.
id columns are the table name singular "_id".

For example.

create table users (
        user_id int auto_increment not null,
        username varchar(20)
        );


I have used simple id columns which works fine too but using the  
table_id concept can make things easier down the road.

Perhaps explaining what your current method is might make it easier  
for us to suggest a solution.

Hope it Helps.

On Sep 12, 2009, at 8:17 PM, matt@... wrote:

> Does anyone have any good naming conventions for mysql databases and  
> tables and columns?  I'm developing a complex lamp project now, and  
> my normal convention doesn't seem to want to work too well for this  
> project - there are a few conflicts.
>
> _______________________________________________
> 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

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

http://www.nyphp.org/show_participation.php

[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

_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Matt Juszczak]

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

Well, this is all in an example.  Even if a customer could have more than
one accounts, allowing for a many:many, I'd still have the same issue
figuring out naming schemes.

> What is a service?

"Email Hosting", "Web Hosting", etc.  But I'm not designing a billing
system.  This is just an example.  But I'd have these same questions if I
was listing something else, like a forum, etc.
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Russ Demarest]

Well a customer to account would be a one to many.

customers
        customer_id
        name
        ....

accounts
        account_id
        name
        customer_id
        ...

services
        service_id
        name
        ...

account_services
        account_service_id
        account_id
        service_id
        start_date
        end_date
        ...

So you have a one to many customers to accounts relation. You have a  
one to many accounts to account_services and a one to many services to  
account_services.

Not sure if I am helping :/

Good luck

On Sep 12, 2009, at 10:02 PM, Matt Juszczak wrote:

>> 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.
>
> Well, this is all in an example.  Even if a customer could have more  
> than one accounts, allowing for a many:many, I'd still have the same  
> issue figuring out naming schemes.
>
>> What is a service?
>
> "Email Hosting", "Web Hosting", etc.  But I'm not designing a  
> billing system.  This is just an example.  But I'd have these same  
> questions if I was listing something else, like a forum, etc.
> _______________________________________________
> 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

[Tim Lieberman-2]

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


_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Matt Juszczak]

> account_services
> account_service_id
> account_id
> service_id
> start_date
> end_date
> ...
>
> So you have a one to many customers to accounts relation. You have a one to
> many accounts to account_services and a one to many services to
> account_services.

Right, although there, I'd probably do it a different way, where services
would be the listing of service  and service_definition would be the
definitions of services.  That way, each record in "service" would have
definition_id which would map to service_definition.id.
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Matt Juszczak]

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

Makes sense :)

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

Agreed.

OK, thanks!  Turns out, I've sort of been doing things this way all along,
minus the _ issue.  Thanks for your input!
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Russ Demarest]

I think the definition would be in the services table.
The entity is a service, hosting or something. The columns in services  
should define the entity (serivce).
So the service_id in account_services ties back to what the service is.



On Sep 12, 2009, at 10:27 PM, Matt Juszczak wrote:

>> account_services
>> account_service_id
>> account_id
>> service_id
>> start_date
>> end_date
>> ...
>>
>> So you have a one to many customers to accounts relation. You have  
>> a one to many accounts to account_services and a one to many  
>> services to account_services.
>
> Right, although there, I'd probably do it a different way, where  
> services would be the listing of service  and service_definition  
> would be the definitions of services.  That way, each record in  
> "service" would have definition_id which would map to  
> service_definition.id.
> _______________________________________________
> 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

[Daniel Convissor-2]

Hi Matt:

Here's my input on this same topic from a couple weeks ago on this list:
http://lists.nyphp.org/pipermail/talk/2009-August/028910.html

Good night,

--Dan

--
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Matt Juszczak]

> Here's my input on this same topic from a couple weeks ago on this list:
> http://lists.nyphp.org/pipermail/talk/2009-August/028910.html

I must have missed that.  Sorry for the repost.

So you would recommend using tablename_ in the beginning of every field in
a table?
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[David Krings]

Tim Lieberman wrote:
> In my experience, the most important thing is consistency.  Almost
> everything else is a matter of taste.

Exactly!

> However, it can create a lot of typing, which can be annoying.

While that is a good warning, it shouldn't be a reason to shy away from
clearly named tables and columns. I think it is better to type a bit more than
keep guessing that column drvtsn is for driver_trip_sheet_number. Depending on
which tool is used to craft the queries you may have intellisense and the
typing is less of an issue after a while.

Keep in mind that there is a chance that someone other than yourself has to
deal with the tables and code later. And even you will be happier when you
don't have to permanently guess half a year from now. I think it is better to
deal with the annoyance of typing than with the annoyance of ambiguitiy. A
mistyped column name will make your query fail, ambiguity will possibly not
while being still wrong.

Other than that, Tim covered it all.

David
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Tedd-3]

At 9:09 PM -0400 9/12/09, Matt Juszczak wrote:

>For instance:
>
>CREATE TABLE `account` (
>-snip-
>
>CREATE TABLE `account_type` (
>-snip-
>
>CREATE TABLE `customer` (
>-snip-
>
>As you can see, each customer as an account_id.  But each account
>also has type_id.  I chose to do this like I did above, but why
>didn't I call the tables:
>
>customer
>customer_account
>customer_account_type
>
>After all, that would have worked the same way, right?

No.

I see what you are saying, but "customer" is as different as
"account" is to "account_type". They are all different things. Why
confuse the matter by adding a customer prefix? There is no need.

Plus, if you're going to be consistent with that "mistake", then your
naming should be:

customer_customer
customer_account
customer_account_type

Do you see what I mean? That practice doesn't provide any clarity --
it only confuses things.

One additional consideration, which was discussed last week, was not
entitling the index of all tables as "id", but rather adding the
table name to the id, such as:

customer_id
account_id
account_type_id

That makes sense to me because when you're dealing with a bunch of
tables, a variable named "id" might be used incorrectly OR
overwritten.

For example, using:

SELECT * FROM customer WHERE customer_id = '$customer_id'

is just as easy to read as:

SELECT * FROM customer WHERE id = '$id'

But later in the code, after reading many different tables, what
table does "$id" relate to? It's not clear is it? But there is no
confusion with $customer_id is there?

HTH's

tedd
--
-------
http://sperling.com  http://ancientstones.com  http://earthstones.com
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Matt Juszczak]

> Plus, if you're going to be consistent with that "mistake", then your naming
> should be:
>
> customer_customer
> customer_account
> customer_account_type

I disagree.  I wasn't trying to create "customer" as a prefix.  I was
simply renaming the tables based on the one:many relationships I have
inside the tables.

account
account_type
customer

since customer stores an account_id, and account stores an account_type
id, I could have picked customer to be the main level table, and just
references out from there:

customer.account_id -> account.id ->
  account.type_id -> account_type.id
  -> ..... ->...
  -> ...... -> ...

Really inefficient, but it was another method of "standardizing" (which to
me, gives no clarity, because as I've explained in one of my other emails,
starting at one top level will make things confusing.  And then what
happens if some new table references customer.id as customer_id?  like an
emails table?  Then I'm really confused:

email
email_customer
email_customer_account
email_customer_account_type

I know this is messed up, but it was my thinking for a while as I tried to
create a "Perfect" relational standard.  And I had no many to many
relationships at that point, so it was really easy to do, but very
ineffective.

> One additional consideration, which was discussed last week, was not
> entitling the index of all tables as "id", but rather adding the table name
> to the id, such as:
>
> customer_id
> account_id
> account_type_id
>
> That makes sense to me because when you're dealing with a bunch of tables, a
> variable named "id" might be used incorrectly OR overwritten.

This is what I've usually done in the past.

> For example, using:
>
> SELECT * FROM customer WHERE customer_id = '$customer_id'
>
> is just as easy to read as:
>
> SELECT * FROM customer WHERE id = '$id'
>
> But later in the code, after reading many different tables, what table does
> "$id" relate to? It's not clear is it? But there is no confusion with
> $customer_id is there?
>
> HTH's

Thanks for your input!

-Matt
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Tedd-3]

At 11:56 AM -0400 9/13/09, Matt Juszczak wrote:

>>Plus, if you're going to be consistent with that "mistake", then
>>your naming should be:
>>
>>customer_customer
>>customer_account
>>customer_account_type
>
>I disagree.  I wasn't trying to create "customer" as a prefix.  I
>was simply renaming the tables based on the one:many relationships I
>have inside the tables.
>
>account
>account_type
>customer
>
>since customer stores an account_id, and account stores an
>account_type id, I could have picked customer to be the main level
>table, and just references out from there:

Mat:

Main level table?

I think that's one of the problems. There is no main level table --
there are just tables. It should not make any difference if you are
addressing customers, accounts, account_types, emails, or whatever.
They are nothing more than data and each has there own relationships.

Also, I think I see another problem. The account table holds the
account_type, right?

If so, then your customer table should only contain the account_id,
but NOT the account_type_id -- that's redundant.

To access what account-type the customer has means you pull the
account_id from the customer table -- then look up that account
(using the account_id ) in the account table -- then pull the
account_type_id and then find the account-type via it's id
(account_type_id) from the account type table. Understand.

customer: account_id
account: account_type_id
account_type: type

In any event, that's the way I would do it.

Cheers,

tedd

--
-------
http://sperling.com  http://ancientstones.com  http://earthstones.com
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

[Kristina Anderson]

You could have a table account_type which has primary key
account_type_id, and a table account which has primary key account_id
and then a lookup field in account which holds the relevant
account_type_id...

That way in table customer you just need a lookup field on account_id
because there is already a relationship in place to find the type of
account based on that value...I think that's what Tedd just said in
essence as well.

Although this structure is certainly presupposing that each customer
has only one account.

Kristina

> At 11:56 AM -0400 9/13/09, Matt Juszczak wrote:
> >>Plus, if you're going to be consistent with that "mistake", then
> >>your naming should be:
> >>
> >>customer_customer
> >>customer_account
> >>customer_account_type
> >
> >I disagree.  I wasn't trying to create "customer" as a prefix.  I
> >was simply renaming the tables based on the one:many relationships I
> >have inside the tables.
> >
> >account
> >account_type
> >customer
> >
> >since customer stores an account_id, and account stores an
> >account_type id, I could have picked customer to be the main level
> >table, and just references out from there:
>
> Mat:
>
> Main level table?
>
> I think that's one of the problems. There is no main level table --
> there are just tables. It should not make any difference if you are
> addressing customers, accounts, account_types, emails, or whatever.
> They are nothing more than data and each has there own relationships.
>
> Also, I think I see another problem. The account table holds the
> account_type, right?
>
> If so, then your customer table should only contain the account_id,
> but NOT the account_type_id -- that's redundant.
>
> To access what account-type the customer has means you pull the
> account_id from the customer table -- then look up that account
> (using the account_id ) in the account table -- then pull the
> account_type_id and then find the account-type via it's id
> (account_type_id) from the account type table. Understand.
>
> customer: account_id
> account: account_type_id
> account_type: type
>
> In any event, that's the way I would do it.
>
> Cheers,
>
> tedd
>
> --
> -------
> http://sperling.com  http://ancientstones.com  http://earthstones.com
> _______________________________________________
> 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