If the customer is the one logging in, and the customer can have only one account, it's not theoretically possible for a login to be associated with more than one customer, then.
So I'm back to my original suggestion for a login table. Each time the customer logs in, generate a row in that table. That way you can preserve the login history and you'd have to have that table, anyway, to generate the login_ids. > No. It is the customer logging in, but a customer record can't have more than one account. > > I actually renamed the customer table to contact in my example to make it more clear. Yes, data can be repeated, but hardly ever. > > Matt > > -----Original Message----- > From: "Kristina D. H. Anderson" <k...@kacomputerconsulting.com> > > Date: Sun, 13 Sep 2009 19:34:18 > To: NYPHP Talk<talk@lists.nyphp.org> > Subject: Re: [nyphp-talk] Database, table, and column naming schemes > > > Hmm, OK. So therefore it is not the customer logging in, but some sort > of account rep overseeing multiple customer accounts...? > If that's true, the the customer_id and login_id have only a peripheral > relationship, and there would also be an accountmanager_id or some such. > > Love this stuff. > > Kristina > > > No. Not true. They can create a Login and map it to multiple customer > records. However there can only be one account per customer. > > > > -----Original Message----- > > From: "Kristina D. H. Anderson" <k...@kacomputerconsulting.com> > > > > Date: Sun, 13 Sep 2009 19:22:49 > > To: NYPHP Talk<talk@lists.nyphp.org> > > Subject: Re: [nyphp-talk] Database, table, and column naming schemes > > > > > > I'm not clear on this... You just told me that you had multiple > logins > > for each customer in the customer table. Right? I.e. each time they > > log in, they get a new login_id. > > > > > 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" <k...@kacomputerconsulting.com> > > > > > > Date: Sun, 13 Sep 2009 19:12:34 > > > To: NYPHP Talk<talk@lists.nyphp.org> > > > Subject: Re: [nyphp-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" <k...@kacomputerconsulting.com> > > > > > > > > Date: Sun, 13 Sep 2009 18:46:25 > > > > To: NYPHP Talk<talk@lists.nyphp.org> > > > > Subject: Re: [nyphp-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" <k...@kacomputerconsulting.com> > > > > > > > > > > Date: Sun, 13 Sep 2009 17:40:45 > > > > > To: NYPHP Talk<talk@lists.nyphp.org> > > > > > Subject: Re: [nyphp-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 > > > _______________________________________________ > > > 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
