Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / General DB Topics / General DB Topics / January 2005

Tip: Looking for answers? Try searching our database.

Database design qns

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
- - 23 Jan 2005 00:13 GMT
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.
DA Morgan - 23 Jan 2005 18:48 GMT
> Qn 1:
>
[quoted text clipped - 56 lines]
>
> 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.
Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

- - 24 Jan 2005 00:49 GMT
>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?
DA Morgan - 24 Jan 2005 02:29 GMT
>> 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.
Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

- - 24 Jan 2005 03:08 GMT
>DA Morgan wrote:
> Today you might be nobody@hoem.om
[quoted text clipped - 4 lines]
> 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.
DA Morgan - 24 Jan 2005 04:26 GMT
>> Today you might be nobody@hoem.om
>> But if you drop that account
[quoted text clipped - 7 lines]
> 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.
Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Alan - 24 Jan 2005 13:11 GMT
> >DA Morgan wrote:
> >
[quoted text clipped - 4 lines]
> Can you elaborate?  Is it when two or more people are sharing the email
> address?

For example:
A company's business rule is that it never gives out an address more than
once, and Email addresses are assigned by the system that uses them for IDs,
and the system is programmed to never give out an address more than once,
and no one will ever change any of this.
Gene Wirchenko - 24 Jan 2005 21:31 GMT
>> >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

    People can share an E-mail address.  I do not see it often, but I
do see it.

>> > suitable except in a specifically constrained environment.
>>
[quoted text clipped - 6 lines]
>and the system is programmed to never give out an address more than once,
>and no one will ever change any of this.

    Fine.  Fine.  Fine.  "Now that Smith, Sr. has retired, Smith,
Jr., our new company president, wants his father's old E-mail address
as his."

Sincerely,

Gene Wirchenko
Christopher Browne - 24 Jan 2005 23:25 GMT
After takin a swig o' Arrakan spice grog, Gene Wirchenko <genew@ucantrade.com.NOTHERE> belched out:

>>> >DA Morgan wrote:
>>> >
[quoted text clipped - 18 lines]
> Jr., our new company president, wants his father's old E-mail address
> as his."

Hmm.

My organization has quite a number of "role-based" addresses which
would utterly destroy the notion of uniqueness here.

There's:

techsupport@foo.bar.info, for technical support
custsupport@foo.bar.info, for customer support
dba@foo.bar.info, to get at our DBAs
systems@foo.bar.info, to get at our sysadmins

There are cases where people working for vendors try to email us
individually, which has a tendancy to _break_ because that means that
if I go on vacation, those things the vendors were contacting me about
wind up staying broken until they can get a hold of someone else.
They should instead have sent the message to the "dba" alias.

Things directed to me generally go to "cbbrowne@foo.bar.info"; if I
cared, I could set up "chris" as an alias for "cbbrowne" because I am,
so far, the only Chris around the office.

This means that email address makes a really crummy attempt at a
primary key, and that we've got pretty complex Exim configuration
files :-).
Signature

let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/wp.html
"People  are more  vocally opposed  to fur  than leather  because it's
easier to harass rich women than motorcycle gangs." [bumper sticker]

Alan - 24 Jan 2005 23:42 GMT
> >> >DA Morgan wrote:
> >> >
[quoted text clipped - 22 lines]
>
> Gene Wirchenko

I never said it was a good idea. The OP wanted an example. In your example
(Smith), it _might_ be okay to do what Jr wants- so long as he understands
that any historical information will not be accurate. Sometimes that is
okay. We have a system (at work, and NOT designed by me) that uses email
addresses for logons. In this particular case, the user population is known,
finitie, and historical logon information is not needed. The email/user id
is linked to an internal (business-related) number that is associated to the
user's business. In effect, his logon id is a pseudonym for his _real_ id.
If this user drops out of our company, we don't care if his email address is
used by someone else because the business-related number will be different.
We just don't have any history of who had that email ID before, but we also
don't need it for anything (so far!). Again- not my idea, and not how I
would have done it, but it does work- in our case- so far...<waiting for
other shoe to drop>
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.