[nycphp-talk] Database, table, and column naming schemes
Kristina D. H. Anderson
ka at kacomputerconsulting.com
Sun Sep 13 22:57:45 EDT 2009
You stated previously that each customer/contact cannot have more than
1 account. But, it appears from the below that you need a "contact
name" and then multiple "account names" under it(i.e. business name,
personal account name) associated with the one contact/customer ID --
since they do in some cases have more than 1 account.
So the contact logs on, that goes in the login table with the
contact_id creating a new row, and then the contact_id jas a
relationship with all existing accounts for that contact. But you'd
still need to historically track the logons for each contact.
Kristina
> 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
> _______________________________________________
> 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