NYCPHP Meetup

[matt at atopia.net]

Tedd,

There are always top level tables in your schema, at least in my opinion. For instance, reference tables that do key -> value pairs tend to not be "root" level tables in a schema. Just my opinion. 

Also, I never said account_type would be stored inside the customer table. If you meant my reference to customer_account_type, that was just a naming scheme for a table only, which I was referring to as inefficient but would be a natural progression if I was to be obsessive about always documenting table relationships inside the table names. 

Based on the feedback I have gotten in this thread, the way I have done things in the past is okay, except ill now be adding underscores to column names for more than just foreign key identifiers. 

Thanks all!

Matt 

-----Original Message-----
From: matt at atopia.net

Date: Mon, 14 Sep 2009 02:03:21 
To: NYPHP Talk<talk at lists.nyphp.org>
Subject: Re: [nycphp-talk] Database, table, and column naming schemes


Login ID is a field inside customer and can be set multiple times per customer record. 

-----Original Message-----
From: "Kristina D. H. Anderson" <ka at kacomputerconsulting.com>

Date: Sun, 13 Sep 2009 18:46:25 
To: NYPHP Talk<talk at lists.nyphp.org>
Subject: Re: [nycphp-talk] Database, table, and column naming schemes


OK.  Is login_id equivalent to customer_id, or is it generated anew 
upon each login and then associated with  a customer profile?  Does 
each customer have only 1 account?

Kristina

> Right.  I want to do it that way on purpose. Because where I tie the 
accounts together is by login id. But most of the time the customer 
information changes per account even if its the same person. 
> 
> -----Original Message-----
> From: "Kristina D. H. Anderson" <ka at kacomputerconsulting.com>
> 
> Date: Sun, 13 Sep 2009 17:40:45 
> To: NYPHP Talk<talk at lists.nyphp.org>
> Subject: Re: [nycphp-talk] Database, table, and column naming schemes
> 
> 
> 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
> _______________________________________________
> 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
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

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