Table schema for user login system?

[klenwell]

I'm in the process of refactoring the php code base I've amassed over
the last few years into an object-oriented framework. I'm about to
start in on the authentication/login extension and I've been thinking
about different approaches to the mysql table schema that stores basic
user login information. At present, user authentication is keyed to a
table with the following columns:

TABLE: basic_user
uid - int [primary]
handle - varchar [unique]*
email - varchar [unique]
password - varchar
access_lvl - tinyint
acct_status - tinyint
confirm_key - varchar
confirmed_timestamp - int
created_timestamp - int

* handle (user name) is optional -- email address alternatively can be
used as the handle

My strategy is to keep this table as minimal as possible and define it
as a kind of constant or standard for the framework. Then, other
tables can be created on a project-by-project basis to link to this
table and expand a user's information, if necessary. (Two examples
would be a profile table for expanding user profile info and an
activity log table to track user activity.)

I was interested in hearing some of the views of others who have
tackled this issue and how you've addressed it. Are there other
columns you like to include in your basic user table? What's your
table schema like?

Tom

[Sanders Kaufman]

"klenwell" <kl******@gmail.comwrote in message
news:11**********************@22g2000hsm.googlegro ups.com...

I'm in the process of refactoring the php code base I've amassed over
the last few years into an object-oriented framework. I'm about to
start in on the authentication/login extension and I've been thinking
about different approaches to the mysql table schema that stores basic
user login information. At present, user authentication is keyed to a
table with the following columns:

TABLE: basic_user
uid - int [primary]
handle - varchar [unique]*
email - varchar [unique]
password - varchar
access_lvl - tinyint
acct_status - tinyint
confirm_key - varchar
confirmed_timestamp - int
created_timestamp - int

* handle (user name) is optional -- email address alternatively can be
used as the handle

My strategy is to keep this table as minimal as possible and define it
as a kind of constant or standard for the framework. Then, other
tables can be created on a project-by-project basis to link to this
table and expand a user's information, if necessary. (Two examples
would be a profile table for expanding user profile info and an
activity log table to track user activity.)

I was interested in hearing some of the views of others who have
tackled this issue and how you've addressed it. Are there other
columns you like to include in your basic user table? What's your
table schema like?

I went up and down that development path for years before I realized that,
as you mentioned, the login accounts table should be minimal... more minimal
than you described, certainly.

I try not to make it any bigger than userid and password - putting all that
other stuff into linked tables.
That way, later, if you cange the other stuff, you don't have to mess with
the actual login table.

[bob.chatman]

On Sep 28, 9:42 pm, klenwell <[e-mail]:

On Sep 28, 5:39 pm, "Sanders Kaufman" <[e-mail]:

"klenwell" <[e-mail] in message

news:11**********************@22g2000hsm.googlegro ups.com...

I went up and down that development path for years before I realized that
...
`user_editcount` int(11) default NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Tom

This is actually very interesting. I have been studying user systems a
lot over the last year or so and have never seen minimal tables in any
of the active/popular applications. Even the PHPBB3 is a huge offender
to your suggestion with their whopping 72 fields in their user table.
I have never really had any issues with dealing with tables with a
large number of entries, assuming they arent replicated or whatever.
In fact, i have only ever read about/seen/been forced to use linked
tables when data is replicated.

Can you provide any reasoning for your minimal approach?

[Dikkie Dik]

This is actually very interesting. I have been studying user systems a

lot over the last year or so and have never seen minimal tables in any
of the active/popular applications. Even the PHPBB3 is a huge offender
to your suggestion with their whopping 72 fields in their user table.
I have never really had any issues with dealing with tables with a
large number of entries, assuming they arent replicated or whatever.
In fact, i have only ever read about/seen/been forced to use linked
tables when data is replicated.

Can you provide any reasoning for your minimal approach?

Login tables tend to store a lot of things that just do not belong
there. An account is just an account. It is not a user, and it is not an
activation. These things are usually separate, or should be. I have had
to cope with too many systems that abused a user table as an account
table. This may sound nice, until you find out that:
- There can be more types of account per user (say, a web account and an
office account. Activations are totally different for "near" or remote
accounts)
- Not every user has these account
- Some accounts may link to more than one table that could be seen as a
user table: A web site may know users and suppliers. At some point,
somebody requires that the suppliers can log in as well. I leave the
rest up to your imagination.

All the above examples are real and I have had to deal with them in the
past months.

[Jerry Stuckle]

Dikkie Dik wrote:

>This is actually very interesting. I have been studying user systems a
lot over the last year or so and have never seen minimal tables in any
of the active/popular applications. Even the PHPBB3 is a huge offender
to your suggestion with their whopping 72 fields in their user table.
I have never really had any issues with dealing with tables with a
large number of entries, assuming they arent replicated or whatever.
In fact, i have only ever read about/seen/been forced to use linked
tables when data is replicated.

Can you provide any reasoning for your minimal approach?

Login tables tend to store a lot of things that just do not belong
there. An account is just an account. It is not a user, and it is not an
activation. These things are usually separate, or should be. I have had
to cope with too many systems that abused a user table as an account
table. This may sound nice, until you find out that:

It depends on how you define "account" and "user". In many systems,
each account is considered a different user.

Also, whether the account is activated or not is an attribute to that
account.

- There can be more types of account per user (say, a web account and an
office account. Activations are totally different for "near" or remote
accounts)
- Not every user has these account
- Some accounts may link to more than one table that could be seen as a
user table: A web site may know users and suppliers. At some point,
somebody requires that the suppliers can log in as well. I leave the
rest up to your imagination.

Sure. But a proper database design and you don't have these problems.

All the above examples are real and I have had to deal with them in the
past months.

Only the past months? They've been going on for the 20 years I've
been involved in RDB design/development. And they aren't going to go away.

But many of these problems also occur because the system needs changed
over the years. Things were correct when they were first laid out, but
as things changed, management tried to modify existing code to "make it
fit", rather than take a look at redesigning the system to make it work.

Such decisions are almost always cheap in the short term, but expensive
in the long term.

The answer is proper system design - including the database. And you
can't make one rule which covers all solutions.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Bruno Barros]

I, for my framework, put:

ID
USERNAME
PASSWORD
EMAIL

That is for the simple login / logout / register data.
Then, according to the application, there is another user table, which
holds all the user's information. It is always made from scratch as I
never know which fields my customer requires or my code needs ;). For
example, for a social networking site you need about me, musical
interests, ..., but for a customer account on a shop, you needn't such
things. The main users table only stores what is essential in ALL
users. Then I make a relation between them, connecting users by ID.
The record with ID X on main table belongs to ID X on the other table.

By the way, PHPBB3, MediaWiki and such are not frameworks, they are
not made to be as broad as possible. They are ONLY made for themselves
and their specific needs, while a framework needs us to remember that
it has to work with all websites from top to bottom.

---
Bruno Rafael Moreira de Barros

Adobe Photoshop CS2 and CS3
-
XML / XSLT
-
MySQL / SQLite / TerraDB
-
PHP 3, 4, 5 and 6

:: Looking For A Permanent Job ::
---

[bob.chatman]

On Sep 29, 6:57 am, Bruno Barros <[e-mail]:

I, for my framework, put:

ID
USERNAME
PASSWORD
EMAIL

That is for the simple login / logout / register data.
Then, according to the application, there is another user table, which
holds all the user's information. It is always made from scratch as I
never know which fields my customer requires or my code needs ;). For
example, for a social networking site you need about me, musical
interests, ..., but for a customer account on a shop, you needn't such
things. The main users table only stores what is essential in ALL
users. Then I make a relation between them, connecting users by ID.
The record with ID X on main table belongs to ID X on the other table.

By the way, PHPBB3, MediaWiki and such are not frameworks, they are
not made to be as broad as possible. They are ONLY made for themselves
and their specific needs, while a framework needs us to remember that
it has to work with all websites from top to bottom.

---
Bruno Rafael Moreira de Barros

Adobe Photoshop CS2 and CS3
-
XML / XSLT
-
MySQL / SQLite / TerraDB
-
PHP 3, 4, 5 and 6

:: Looking For A Permanent Job ::
---

I wasnt really worried about frameworks, but i dont know of any
framework that has details directing the developers to keep the user
tables small. That aside, is the summation of the above to do what
your application needs? I can understand the situation where your user
has multiple accounts, but i dont think that really has much bearing
on most web applications; as Jerry brought up -

It depends on how you define "account" and "user". In many
systems, each account is considered a different user.

Ive
actually found that most applications do treat "accounts" and "users"
as one mass, and especially so in my discussions with a few folks on
the oncoming wave of Open ID compliant applications. Are there any
rules of thumb that have come up? this is of course aside from "if it
occurs more than once you should probably be using another table."

[Jerry Stuckle]

Bruno Barros wrote:

I, for my framework, put:

ID
USERNAME
PASSWORD
EMAIL

That is for the simple login / logout / register data.
Then, according to the application, there is another user table, which
holds all the user's information. It is always made from scratch as I
never know which fields my customer requires or my code needs ;). For
example, for a social networking site you need about me, musical
interests, ..., but for a customer account on a shop, you needn't such
things. The main users table only stores what is essential in ALL
users. Then I make a relation between them, connecting users by ID.
The record with ID X on main table belongs to ID X on the other table.

By the way, PHPBB3, MediaWiki and such are not frameworks, they are
not made to be as broad as possible. They are ONLY made for themselves
and their specific needs, while a framework needs us to remember that
it has to work with all websites from top to bottom.

---
Bruno Rafael Moreira de Barros

Adobe Photoshop CS2 and CS3
-
XML / XSLT
-
MySQL / SQLite / TerraDB
-
PHP 3, 4, 5 and 6

:: Looking For A Permanent Job ::
---

That's one way to do it. But it's not necessarily a good database
design. If there is information specific to that user, I include it in
the same table. Among other things, it saves unnecessary joins.

But this doesn't belong in a PHP newsgroup, anyway. It's more
applicable to a database newsgroup.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Sanders Kaufman]

<bo*********@gmail.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...

This is actually very interesting. I have been studying user systems a
lot over the last year or so and have never seen minimal tables in any
of the active/popular applications.
of the active/popular applications. Even the PHPBB3 is a huge offender
to your suggestion with their whopping 72 fields in their user table.
I have never really had any issues with dealing with tables with a
large number of entries, assuming they arent replicated or whatever.
In fact, i have only ever read about/seen/been forced to use linked
tables when data is replicated.

Can you provide any reasoning for your minimal approach?

It's a DB theory purist thing - with very few real-world applications.
In fact - I don't think I've *ever* encountered a fully normalized database
in the real world.
But that doesn't stop me from evangelizing normalized data!

Non-normalized data is to Databases what spaghetti code is to language.
It may work fine for the folks who threw the app together, but they're the
ONLY ones who will enjoy it.

The benefits of fully normalized data are most readily apparent
- in massive databases
- or after years of use,
- or in systems that later try to integrate with other systems.

[Sanders Kaufman]

"Bruno Barros" <ra*****@gmail.comwrote in message
news:11**********************@o80g2000hse.googlegr oups.com...

I, for my framework, put:

ID
USERNAME
PASSWORD
EMAIL

That is for the simple login / logout / register data.
Then, according to the application, there is another user table, which
holds all the user's information. It is always made from scratch as I
never know which fields my customer requires or my code needs ;). For
example, for a social networking site you need about me, musical
interests, ..., but for a customer account on a shop, you needn't such
things. The main users table only stores what is essential in ALL
users. Then I make a relation between them, connecting users by ID.
The record with ID X on main table belongs to ID X on the other table.

By the way, PHPBB3, MediaWiki and such are not frameworks, they are
not made to be as broad as possible. They are ONLY made for themselves
and their specific needs, while a framework needs us to remember that
it has to work with all websites from top to bottom.

I'm woring on a similar situation.
I've got a userid, username, emailaddress and password.

But I also want fields for login_cookie, last_login, and parent_user.
I don't really WANT to create whole nother table to track that stuff, but
good design dictates that I should.

The question becomes - if I break atomicity for expedience's sake, what will
be the consequences?

[Michael Fesser]

..oO(Sanders Kaufman)

><bo*********@gmail.comwrote in message
news:11**********************@k79g2000hse.googleg roups.com...

>Can you provide any reasoning for your minimal approach?

It's a DB theory purist thing - with very few real-world applications.
In fact - I don't think I've *ever* encountered a fully normalized database
in the real world.

A fully normalized database wouldn't make much sense at all or would
cause way too much overhead. Sometimes even a little redundancy can be
much more practical in the real application.

The question is also how you define "fully normalized". Where do you
draw the line? 3NF? BCNF? While the first 3 normal forms more or less
only reduce redundancy, the more higher normal forms may also destroy
dependencies.

>But that doesn't stop me from evangelizing normalized data!

Where's the point in storing the user's email address in another table
instead of the user or account table itself? It only makes sense if
there can be multiple addresses.

>Non-normalized data is to Databases what spaghetti code is to language.

Over-normalized data is to databases what div-soup is to HTML.

Micha

[Michael Fesser]

..oO(Dikkie Dik)

>Login tables tend to store a lot of things that just do not belong
there. An account is just an account. It is not a user, and it is not an
activation.

In my system one account = one user. The accounts table keeps user
names, login names, password hashes, account creation and expiration
dates, date of last login, privilege and status flags and such things.

Additional user data like special profiles, addresses, contacts etc. are
stored in other tables.

>- There can be more types of account per user (say, a web account and an
office account. Activations are totally different for "near" or remote
accounts)

I can apply different roles (user groups) to each account.

Micha

[Jerry Stuckle]

Sanders Kaufman wrote:

"Bruno Barros" <ra*****@gmail.comwrote in message
news:11**********************@o80g2000hse.googlegr oups.com...

>I, for my framework, put:

ID
USERNAME
PASSWORD
EMAIL

That is for the simple login / logout / register data.
Then, according to the application, there is another user table, which
holds all the user's information. It is always made from scratch as I
never know which fields my customer requires or my code needs ;). For
example, for a social networking site you need about me, musical
interests, ..., but for a customer account on a shop, you needn't such
things. The main users table only stores what is essential in ALL
users. Then I make a relation between them, connecting users by ID.
The record with ID X on main table belongs to ID X on the other table.

By the way, PHPBB3, MediaWiki and such are not frameworks, they are
not made to be as broad as possible. They are ONLY made for themselves
and their specific needs, while a framework needs us to remember that
it has to work with all websites from top to bottom.

I'm woring on a similar situation.
I've got a userid, username, emailaddress and password.

But I also want fields for login_cookie, last_login, and parent_user.
I don't really WANT to create whole nother table to track that stuff, but
good design dictates that I should.

And what "good design" is that? Definitely not normalization.

The question becomes - if I break atomicity for expedience's sake, what will
be the consequences?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Bruno Barros]

That's one way to do it. But it's not necessarily a good database

design. If there is information specific to that user, I include it in
the same table. Among other things, it saves unnecessary joins.

No need to join, just fetch both tables and process the results. Then,

$who_is_logged_in == 1; // Just an example saying the user that is
logged has an ID of 1.
$user_who_is_logged_in_data ==
$data_from_second_table[$who_is_logged_in];

I prefer to do this way. And what I meant was not to do using two
tables, but that it would be more profitable on an abstraction basis,
as you would never have to deal with the first table, which was
absolutely important. Of course you don't need to use my method,
afterall, it's my framework ;).

But this doesn't belong in a PHP newsgroup, anyway. It's more
applicable to a database newsgroup.

Doubt it. All they could do was tell you the best way to link the
tables and do all that DB stuff. What we PHPers want is the best
minimal way to get a working user system.

---
Bruno Rafael Moreira de Barros

Adobe Photoshop CS2 and CS3
-
XML / XSLT
-
MySQL / SQLite / TerraDB
-
PHP 3, 4, 5 and 6

:: Looking For A Permanent Job ::
---

[bob.chatman]

On Sep 29, 1:48 pm, Bruno Barros <[e-mail]:

That's one way to do it. But it's not necessarily a good database
design. If there is information specific to that user, I include it in
the same table. Among other things, it saves unnecessary joins.

No need to join, just fetch both tables and process the results. Then,

$who_is_logged_in == 1; // Just an example saying the user that is
logged has an ID of 1.
$user_who_is_logged_in_data ==
$data_from_second_table[$who_is_logged_in];

I prefer to do this way. And what I meant was not to do using two
tables, but that it would be more profitable on an abstraction basis,
as you would never have to deal with the first table, which was
absolutely important. Of course you don't need to use my method,
afterall, it's my framework ;).

But this doesn't belong in a PHP newsgroup, anyway. It's more
applicable to a database newsgroup.

Doubt it. All they could do was tell you the best way to link the
tables and do all that DB stuff. What we PHPers want is the best
minimal way to get a working user system.

---
Bruno Rafael Moreira de Barros

Adobe Photoshop CS2 and CS3
-
XML / XSLT
-
MySQL / SQLite / TerraDB
-
PHP 3, 4, 5 and 6

:: Looking For A Permanent Job ::
---

Doesnt fetching both tables kill the logic that is provided in keeping
your data separate? when i first started studying application design
my teacher sat down and explained that if there is a way to simplify
your application you should do it, and i see what you have said here
to be kind of difficult to parse.

Why would you not take advantage of something the database provides,
even if you are able to handle processing in your application, since
you would be using a smaller data set and most likely focusing on the
data that is actually being used on that page? I would be really weary
of an application that just grabbed everything from the database and
processed it in its own code when it didnt need to. Ive seen it a
couple times and as time goes on, as has been brought up above, your
application will lose its responsiveness and eventually become
useless.

im glad that we have brought things back to the PHP end of things, but
i dont want to come off half cocked. I am certain that application
design is much more than the language you choose to build it in, and
if you are going to say that grabbing all your information at once and
processing it in your framework is your choice means of doing things,
but i would really like to see the benchmarks that would support that
means of development. I am fairly sure that your framework is playing
with fire, and i fear for anyone who chooses to follow that pattern of
development.

SQL is an extensive language so you don't have to bother dealing with
most processing issues, such as the one you have shown here.

[Bruno Barros]

On 30 Sep, 09:12, "[e-mail]" <[e-mail]>
wrote:

On Sep 29, 1:48 pm, Bruno Barros <[e-mail]:

That's one way to do it. But it's not necessarily a good database
design. If there is information specific to that user, I include it in
the same table. Among other things, it saves unnecessary joins.

No need to join, just fetch both tables and process the results. Then,

$who_is_logged_in == 1; // Just an example saying the user that is
logged has an ID of 1.
$user_who_is_logged_in_data ==
$data_from_second_table[$who_is_logged_in];

I prefer to do this way. And what I meant was not to do using two
tables, but that it would be more profitable on an abstraction basis,
as you would never have to deal with the first table, which was
absolutely important. Of course you don't need to use my method,
afterall, it's my framework ;).

But this doesn't belong in a PHP newsgroup, anyway. It's more
applicable to a database newsgroup.

Doubt it. All they could do was tell you the best way to link the
tables and do all that DB stuff. What we PHPers want is the best
minimal way to get a working user system.

---
Bruno Rafael Moreira de Barros

Adobe Photoshop CS2 and CS3
-
XML / XSLT
-
MySQL / SQLite / TerraDB
-
PHP 3, 4, 5 and 6

:: Looking For A Permanent Job ::
---

Doesnt fetching both tables kill the logic that is provided in keeping
your data separate? when i first started studying application design
my teacher sat down and explained that if there is a way to simplify
your application you should do it, and i see what you have said here
to be kind of difficult to parse.

Why would you not take advantage of something the database provides,
even if you are able to handle processing in your application, since
you would be using a smaller data set and most likely focusing on the
data that is actually being used on that page? I would be really weary
of an application that just grabbed everything from the database and
processed it in its own code when it didnt need to. Ive seen it a
couple times and as time goes on, as has been brought up above, your
application will lose its responsiveness and eventually become
useless.

im glad that we have brought things back to the PHP end of things, but
i dont want to come off half cocked. I am certain that application
design is much more than the language you choose to build it in, and
if you are going to say that grabbing all your information at once and
processing it in your framework is your choice means of doing things,
but i would really like to see the benchmarks that would support that
means of development. I am fairly sure that your framework is playing
with fire, and i fear for anyone who chooses to follow that pattern of
development.

SQL is an extensive language so you don't have to bother dealing with
most processing issues, such as the one you have shown here.

Yes I know, but I just spoken about that for the way of separating
things (the user login and the user data). Of course it can be done, I
just explained the PHP way of doing it.

[Bruno Barros]

Ouch - deleting an account is a bad way to disable a login.

Better to reduce the privledges for the account.

If you have a privileges system. Else, you should just have a BANNED
field that's a boolean :).

[bob.chatman]

On Sep 30, 5:45 am, "Sanders Kaufman" <[e-mail]:

"Jerry Stuckle" <[e-mail] in message

news:We******************************@comcast.com. ..

Sanders Kaufman wrote:

I'm woring on a similar situation.
I've got a userid, username, emailaddress and password.

But I also want fields for login_cookie, last_login, and parent_user.
I don't really WANT to create whole nother table to track that stuff, but
good design dictates that I should.

And what "good design" is that? Definitely not normalization.

Definitely normalization.

The question becomes - if I break atomicity for expedience's sake, what
will be the consequences?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Has anyone got any benchmarks or profiling data to back up their
claims? i have never noticed any performance increases based on
setting up a database to be set up in the, for lack of a better label,
"Everything belonging to the user in the user table" form versus the
"Spread it out and call it normalized" form. There is of course merit
to both options, but i think that it would be better to stop
speculating and start supporting the information.

I am working on a paper on this stuff so if i try to back something up
with "Sanders Kaufman" or "Bruno Barros" said it wont come across very
well.

[Jerry Stuckle]

bo*********@gmail.com wrote:

On Sep 30, 5:45 am, "Sanders Kaufman" <[e-mail]:

>"Jerry Stuckle" <[e-mail] in message

news:We******************************@comcast.com ...

>>Sanders Kaufman wrote:
I'm woring on a similar situation.
I've got a userid, username, emailaddress and password.
But I also want fields for login_cookie, last_login, and parent_user.
I don't really WANT to create whole nother table to track that stuff, but
good design dictates that I should.
And what "good design" is that? Definitely not normalization.

Definitely normalization.

>>>The question becomes - if I break atomicity for expedience's sake, what
will be the consequences?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Has anyone got any benchmarks or profiling data to back up their
claims? i have never noticed any performance increases based on
setting up a database to be set up in the, for lack of a better label,
"Everything belonging to the user in the user table" form versus the
"Spread it out and call it normalized" form. There is of course merit
to both options, but i think that it would be better to stop
speculating and start supporting the information.

I am working on a paper on this stuff so if i try to back something up
with "Sanders Kaufman" or "Bruno Barros" said it wont come across very
well.

I could comment in more detail on this, with a lot of examples.
However, this is not the correct newsgroup for discussion database
design. I would be interested in discussing it more in an appropriate
newsgroup, such as comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Bruno Barros]

On 30 Sep, 19:09, "[e-mail]" <[e-mail]>
wrote:

On Sep 30, 5:45 am, "Sanders Kaufman" <[e-mail]:

"Jerry Stuckle" <[e-mail] in message

news:We******************************@comcast.com. ..

Sanders Kaufman wrote:
>I'm woring on a similar situation.
>I've got a userid, username, emailaddress and password.

>But I also want fields for login_cookie, last_login, and parent_user.
>I don't really WANT to create whole nother table to track that stuff, but
>good design dictates that I should.

And what "good design" is that? Definitely not normalization.

Definitely normalization.

>The question becomes - if I break atomicity for expedience's sake, what
>will be the consequences?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Has anyone got any benchmarks or profiling data to back up their
claims? i have never noticed any performance increases based on
setting up a database to be set up in the, for lack of a better label,
"Everything belonging to the user in the user table" form versus the
"Spread it out and call it normalized" form. There is of course merit
to both options, but i think that it would be better to stop
speculating and start supporting the information.

I am working on a paper on this stuff so if i try to back something up
with "Sanders Kaufman" or "Bruno Barros" said it wont come across very
well.

I never said there would be performance differences, nor I named it
normalization. I named it keep the essential table a MUST for ALL
applications using the framework and then, according to the
applications' needs, use another table with the data needed. Not all
applications need the same amount of information! For example, a
customer needs to give away his phone number to be contacted, but a
guy registering for a forum doesn't have to! And then, instead having
a big fat table with a load of fields that might or might not be used
for the applications, you only had what was really necessary!

[Jerry Stuckle]

Bruno Barros wrote:

On 30 Sep, 19:09, "[e-mail]" <[e-mail]>
wrote:

>On Sep 30, 5:45 am, "Sanders Kaufman" <[e-mail]:

>>"Jerry Stuckle" <[e-mail] in message
news:We******************************@comcast.co m...
Sanders Kaufman wrote:
I'm woring on a similar situation.
I've got a userid, username, emailaddress and password.
But I also want fields for login_cookie, last_login, and parent_user.
I don't really WANT to create whole nother table to track that stuff, but
good design dictates that I should.
And what "good design" is that? Definitely not normalization.
Definitely normalization.
The question becomes - if I break atomicity for expedience's sake, what
will be the consequences?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Has anyone got any benchmarks or profiling data to back up their
claims? i have never noticed any performance increases based on
setting up a database to be set up in the, for lack of a better label,
"Everything belonging to the user in the user table" form versus the
"Spread it out and call it normalized" form. There is of course merit
to both options, but i think that it would be better to stop
speculating and start supporting the information.

I am working on a paper on this stuff so if i try to back something up
with "Sanders Kaufman" or "Bruno Barros" said it wont come across very
well.

I never said there would be performance differences, nor I named it
normalization. I named it keep the essential table a MUST for ALL
applications using the framework and then, according to the
applications' needs, use another table with the data needed. Not all
applications need the same amount of information! For example, a
customer needs to give away his phone number to be contacted, but a
guy registering for a forum doesn't have to! And then, instead having
a big fat table with a load of fields that might or might not be used
for the applications, you only had what was really necessary!

But I called it what it is - an example of overnormalization which
causes more performance problems. And stupid.

But again, this a PHP group, not a database group. If you want to
continue this inane discourse, please take it to a database newsgroup -
where you will be told why you're wrong by a lot of database experts.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Rik Wasmus]

On Mon, 01 Oct 2007 11:16:17 +0200, Bruno Barros <ra*****@gmail.comwrote:

On 30 Sep, 19:09, "[e-mail]" <[e-mail]>
wrote:

>On Sep 30, 5:45 am, "Sanders Kaufman" <[e-mail]:

"Jerry Stuckle" <[e-mail] in message

>news:We******************************@comcast.com ...

Sanders Kaufman wrote:
I'm woring on a similar situation.
I've got a userid, username, emailaddress and password.

>But I also want fields for login_cookie, last_login, and

parent_user.

>I don't really WANT to create whole nother table to track that

stuff, but

>good design dictates that I should.

And what "good design" is that? Definitely not normalization.

Definitely normalization.

>The question becomes - if I break atomicity for expedience's sake,

what

>will be the consequences?

Has anyone got any benchmarks or profiling data to back up their
claims? i have never noticed any performance increases based on
setting up a database to be set up in the, for lack of a better label,
"Everything belonging to the user in the user table" form versus the
"Spread it out and call it normalized" form. There is of course merit
to both options, but i think that it would be better to stop
speculating and start supporting the information.

I am working on a paper on this stuff so if i try to back something up
with "Sanders Kaufman" or "Bruno Barros" said it wont come across very
well.

I never said there would be performance differences, nor I named it
normalization. I named it keep the essential table a MUST for ALL
applications using the framework and then, according to the
applications' needs, use another table with the data needed. Not all
applications need the same amount of information! For example, a
customer needs to give away his phone number to be contacted, but a
guy registering for a forum doesn't have to! And then, instead having
a big fat table with a load of fields that might or might not be used
for the applications, you only had what was really necessary!

Who cares there's more information in the database? As long as you just
use 'SELECT only,the,fields,I,need FROM table' instead of a lazy 'SELECT *
FROM table', eveything's going to be speedy and allright. On a side note:
I know forums where a telephone number is needed/required/recommended.
Usually a more closed, select group forum though.
--
Rik Wasmus

[Michael Fesser]

..oO(Rik Wasmus)

>On Mon, 01 Oct 2007 11:16:17 +0200, Bruno Barros <ra*****@gmail.comwrote:

>>
I never said there would be performance differences, nor I named it
normalization. I named it keep the essential table a MUST for ALL
applications using the framework and then, according to the
applications' needs, use another table with the data needed. Not all
applications need the same amount of information! For example, a
customer needs to give away his phone number to be contacted, but a
guy registering for a forum doesn't have to! And then, instead having
a big fat table with a load of fields that might or might not be used
for the applications, you only had what was really necessary!

Who cares there's more information in the database?

I don't want to have 30 fields in a table, if only 5 of them are used in
most cases. In a shop database I surely don't want to have just a single
products table that holds all possible attributes and additional
informations for all the different products. It just keeps the main and
common stuff, while additional informations are stored in separate
tables as necessary (for example track infos for music CDs).

>As long as you just
use 'SELECT only,the,fields,I,need FROM table' instead of a lazy 'SELECT *
FROM table', eveything's going to be speedy and allright.

JOINs on properly indexed tables are fast as well.

>On a side note:
I know forums where a telephone number is needed/required/recommended.
Usually a more closed, select group forum though.

But there could also be a second phone number (private and business for
example), a fax, some mobile numbers, IM names ... clearly enough to use
another table for all the contact data.

Micha