[nycphp-talk] Database, table, and column naming schemes
Kristina D. H. Anderson
ka at kacomputerconsulting.com
Sun Sep 13 22:45:54 EDT 2009
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" <ka at kacomputerconsulting.com>
>
> Date: Sun, 13 Sep 2009 19:34:18
> To: NYPHP Talk<talk at lists.nyphp.org>
> Subject: Re: [nycphp-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" <ka at kacomputerconsulting.com>
> >
> > Date: Sun, 13 Sep 2009 19:22:49
> > To: NYPHP Talk<talk at lists.nyphp.org>
> > Subject: Re: [nycphp-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" <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
> > > _______________________________________________
> > > 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
>
>
More information about the talk
mailing list