Hi,

Not too experienced yet when it comes to database normalization. And right
now I'm working on a project in which I would like to have user profiles:

My first impulse was to create a User table, a Person table and a
Person_profile table

The User table would just have the basics for a user to access in the
system:
- id // unique id
- username
- password
- email // needed for registration verification
- active // is user allowed to login?

The Person table would have extended info about that person:
- id // unique id
- user_id // foreign key User.id
- gender
- firstname
- lastname
- birthdate
- phone
- etc..

The Person_profile table would have stuff like:
- id // unique id
- person_id // foreign key Person.id
- image
- url
- description
- active // is user allowed to have a profile
- etc..

This all seemed logic from a perspective that things should be clarifying
and well structured. Also I'm not to thrilled about having too many columns
in one table (possible performance issues?)
But the more I think about it the more I get the feeling that this approach
is unnecessary, because all the information so far still belongs to only one
user.
And from browsing a little through some recent threads in this group I also
got the impression that the id's in each table is overkill too.

What are some of your views on this structure? Insights are much
appreciated.

Cheers.

amygdala wrote:
duplicates along with the name. In general most sites I have seen key
on only one account per e-mail address. Sometimes a user can forget
their user-name but not their e-mail address, because that is keyed into
their e-mail account. So maybe just using the e-mail address as the key
might be a good idea.

If you use an ID number as they key, one problem can be looking for
duplicates as you enter the data. This is a problem if the key is a
completely incremented field.

amygdala wrote:

1) There is no such thing as a magical, universal id that fits onto
everything in creation.

2) The number of columns is determined by the number of attributes you
wish model, so do not split them into multiple tables.

3) Do not write with bit flags; give an active date or status code.

4) And I like the suggestion of using the email address as a key; it
has validation and verification.

* top posting fixed *

"Last Boy Scout" <BadBill (AT) whitehouse (DOT) gov> schreef in bericht
news:FaBUh.1190$7n.877 (AT) newsfe12 (DOT) lga...
Thanks for your insights.
I hear you. I would have used UNIQUE KEY `email` (`email`) and UNIQUE KEY
`username` (`username`) though. But using email as primary key is probably
better yeah.

PS.: Please don't toppost, as it makes threads difficult to read.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> schreef in bericht
news:1176719210.574486.113260 (AT) e65g2000hsc (DOT) googlegroups.com...

Alright, I will put all attributes in one table then. It makes sense.



What's the reasoning behind this? Is 0 or 1 not a statuscode? (active or
inactive)
The active status should be able to be changed if users are (temporarily)
banned.


I don't like the idea that throughout the site I would have to address user
profiles as:

http://www.somesite.com/getProfile?e...ress (DOT) com

But, come to think of it, I could use username to serve that purpose of
course.

Cheers

On Apr 16, 10:33 am, "amygdala" <nore... (AT) noreply (DOT) com> wrote:
Just keep asking the question: is X an attribute of entity Y?
Every time the answer is yes, put it in that entiry. Later you will
split entities according to the Normalizatio rules. So for example, is
phone number an attribute of entity Person? YES
So you add it to the person entity.

Then later when you realize a person can have more than one phone
number. So you create the phone entity and the relation(should have
been a different word) between person and phone. This is about as
simple as an ERD gets.
If it is temporary, then when do you restore them? how do you know 1
means banned temporarily for user T but permanently for user Q?

What's wrong with it? You might only need it for the inital login.

Ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> schreef in bericht
news:1176746698.530701.83320 (AT) d57g2000hsg (DOT) googlegroups.com...
Alright, gotcha.

As far as I thought of it until now, I would use active or inactive only as
a flag that I could be set, predominantly, manualy if a user has paid
contribution. I don't see this project becoming so complex yet to have it
probe any kind of back office application whether or not user X paid the
bill. That's way out of my league.
Good point though, I agree that using merely 0 or 1 is somewhat restrictive.
Although, since I would use char(1) of int(1) it's use could still be easily
expanded right? And since I am going to be the administrator of the site I
know it's meaning.
But let's say I wanted it to be more meaningful or clarifying (for myself or
a future developer) would you advice an enum column in this case?

What's wrong with using email as an identifier you mean? If so, let's say I
was user X who wants to visit the profile of user Y: I don't want user X to
know what the email address of user Y is. And since I'm building a site for
which you don't need to be logged in to view ones profile, spamming issues
come to mind here also.

Cheers

Last Boy Scout <BadBill (AT) whitehouse (DOT) gov> wrote:

[snip]

Or not. Sometimes, a couple share an E-mail address.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

On Apr 16, 5:51 pm, "amygdala" <nore... (AT) noreply (DOT) com> wrote:
As I said, I was thinking it is only a login ID, so only the owner
would need to know it. Thereafter your have a session ID transmitted
back and forth.

As far as accessing other profiles, that seems to be a new
requirement. It wasn't mentioned in your original post. You need to
think thru your DB access versus your WEB addressing. Or maybe rethink
the email address as PK.

Ed