[nycphp-talk] Database, table, and column naming schemes
matt at atopia.net
matt at atopia.net
Sun Sep 13 22:37:07 EDT 2009
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
More information about the talk
mailing list