[nycphp-talk] Database, table, and column naming schemes
matt at atopia.net
matt at atopia.net
Sun Sep 13 22:51:59 EDT 2009
It is the contact (not customer, I renamed the table) logging in, but perhaps for two different entities.
Here is an example. There is a guy named tom who has a personal webhosting account and a business account for his business. His email addresses are different. So are his names! For his personal account, he uses tom Jones. For his work account, he uses Thomas Jones II. So everything is different, but he is the same person and wants to maintain both accounts with the same username.
-----Original Message-----
From: "Kristina D. H. Anderson" <ka at kacomputerconsulting.com>
Date: Sun, 13 Sep 2009 19:45:54
To: NYPHP Talk<talk at lists.nyphp.org>
Subject: Re: [nycphp-talk] Database, table, and column naming schemes
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
>
>
_______________________________________________
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