 | | From: | - | | Subject: | Database design qns | | Date: | Sun, 23 Jan 2005 08:13:42 +0800 |
|
|
 | Qn 1:
I have a user_account table consisting of an auto id, email address and
password fields.
The id is made the primary key. Is that a good idea or should i just
discard the id and use email address instead?
If I were to have the email address as a foreign key in another table,
will the space consumed bigger that if i were to just use the id?
Which criteria should I give more priority to in deciding which way to go?
Qn 2:
CREATE TABLE user_account
(
id....
email_address ......
password .......
activated...
......
)
where the email_addres serves as the username. When a user signed up for
an account, the 'activated' entry defaults to 'off'. The user will then
have to check his email account and go to a link to activate the account.
What I would like to do is make the email_address changeable and the
user has to verify that the new email addres is valid by again visiting
a link first.
This means that the new email address has to be stored somewhere.
There are two options that i can think of.
1) Create a new table as follows:
CREATE TABLE updated_email_address(
(
user_account_id...
new_email_address...
)
2) Store the new email into the user_account table.
CREATE TABLE user_account
(
id....
email_address ......
new_email_address <--------------
password .......
activated...
......
)
Design wise, which is the prettiest method?
Or is there another solution besides the above two?
Thank you.
|
|
| | From: | DA Morgan | | Subject: | Re: Database design qns | | Date: | Sun, 23 Jan 2005 10:48:49 -0800 |
|
|
| - wrote:
> Qn 1:
>
> I have a user_account table consisting of an auto id, email address and
> password fields.
>
> The id is made the primary key. Is that a good idea or should i just
> discard the id and use email address instead?
>
> If I were to have the email address as a foreign key in another table,
> will the space consumed bigger that if i were to just use the id?
>
> Which criteria should I give more priority to in deciding which way to go?
>
>
> Qn 2:
>
> CREATE TABLE user_account
> (
> id....
> email_address ......
> password .......
> activated...
> ......
> )
>
> where the email_addres serves as the username. When a user signed up for
> an account, the 'activated' entry defaults to 'off'. The user will then
> have to check his email account and go to a link to activate the account.
>
> What I would like to do is make the email_address changeable and the
> user has to verify that the new email addres is valid by again visiting
> a link first.
>
> This means that the new email address has to be stored somewhere.
>
> There are two options that i can think of.
>
> 1) Create a new table as follows:
>
> CREATE TABLE updated_email_address(
> (
> user_account_id...
> new_email_address...
> )
>
> 2) Store the new email into the user_account table.
>
> CREATE TABLE user_account
> (
> id....
> email_address ......
> new_email_address <--------------
> password .......
> activated...
> ......
> )
>
>
> Design wise, which is the prettiest method?
> Or is there another solution besides the above two?
>
> Thank you.
The point of a primary key is to uniquely identify a record for purposes
of data integrity. The amount of space consumed by one vs the other is
irrelevant. Make your decision based on the key serving the purpose.
BTW: It is possible for two different people to have the same email
address: Just not at the same point-in-time. So an email address is NOT
suitable except in a specifically constrained environment.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
|
|
| | From: | - | | Subject: | Re: Database design qns | | Date: | Mon, 24 Jan 2005 08:49:01 +0800 |
|
|
| >DA Morgan wrote:
>
> BTW: It is possible for two different people to have the same email
> address: Just not at the same point-in-time. So an email address is NOT
> suitable except in a specifically constrained environment.
Can you elaborate? Is it when two or more people are sharing the email
address?
|
|
| | From: | DA Morgan | | Subject: | Re: Database design qns | | Date: | Sun, 23 Jan 2005 18:29:03 -0800 |
|
|
| - wrote:
> >DA Morgan wrote:
>
>>
>> BTW: It is possible for two different people to have the same email
>> address: Just not at the same point-in-time. So an email address is NOT
>> suitable except in a specifically constrained environment.
>
>
> Can you elaborate? Is it when two or more people are sharing the email
> address?
Suppose you have the account myemail@aol.com
Next week you leave AOL and get a real ISP
Two weeks later someone else comes along and grabs the now
available email address.
Same thing in a company:
Today you might be nobody@hoem.om
But if you drop that account
A year later someone could easily pick it up.
This is not rocket science you need to carefully think
through the implications of decisions you made and this
was an easy one.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
|
|
| | From: | - | | Subject: | Re: Database design qns | | Date: | Mon, 24 Jan 2005 11:08:15 +0800 |
|
|
| >DA Morgan wrote:
> Today you might be nobody@hoem.om
> But if you drop that account
> A year later someone could easily pick it up.
>
> This is not rocket science you need to carefully think
> through the implications of decisions you made and this
> was an easy one.
I see.. but some major companies, like google, are using email address
as a username when one signs up with their google groups. They leave it
up to the users to 'keep alive' their email address, i reckon.
|
|
| | From: | DA Morgan | | Subject: | Re: Database design qns | | Date: | Sun, 23 Jan 2005 20:26:40 -0800 |
|
|
| - wrote:
> >DA Morgan wrote:
>
>> Today you might be nobody@hoem.om
>> But if you drop that account
>> A year later someone could easily pick it up.
>>
>> This is not rocket science you need to carefully think
>> through the implications of decisions you made and this
>> was an easy one.
>
>
> I see.. but some major companies, like google, are using email address
> as a username when one signs up with their google groups. They leave it
> up to the users to 'keep alive' their email address, i reckon.
If 99 companies never reissued a username but one does ... you MUST
model for the exception. And even if none does but one COULD you
MUST model for the exception.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
|
|
