[nycphp-talk] Database, table, and column naming schemes
Kristina D. H. Anderson
ka at kacomputerconsulting.com
Sun Sep 13 22:22:49 EDT 2009
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
>
>
More information about the talk
mailing list