NYCPHP Meetup

[matt at atopia.net]

But that would also allow multiple logins for the same customer, which I don't. Its a one to many only. So it doesn't need a separate table. 

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

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


If each customer can have more than 1 login ID, then normalization 
dictates a separate table, let's call it login , with fields

login_id
customer_id
login_time
login_IP
session_id

or whatever you store related to Logins, i.e. one row for each time the 
customer logs in, with their permanent customer_id and the assigned 
login_id for that session.

Each time they login, the table generates a new row, with a new 
login_id, and associates it with their customer_id.

So you can then do a query and find ALL the times each customer logged 
in.

Unless you're overwriting the login_id in the customer table each time, 
and not storing the historical data...but usually that would not be the 
case. 

Kristina



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




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

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