Database design qn

[-]

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?

Thanks

[Bill Karwin]

- wrote:

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?
The general wisdom is don't use a column for two things, and don't set
up a system in which you are likely to be changing the value of the
primary key for a given record.

If use use email address for a primary key, and the user changes his/her
address, you'll have to change it in the user_account table and then
cascade in every table that references the user_account table. Also
you'd need to propagate these changes to any applications that work on
copies of this data, and you'd have a hard time if you need to do any
diffs between database backups taken on different dates.

If you make the primary key column a value that has no other meaning
other than to uniquely identify the record, there will never be reason
to change the value, and you can always rely on a given value referring
to the same user_account record.
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?
Most definitely. A 4-byte integer in the foreign key is much smaller
than a copy of an email address, which is a string and almost always
would be 11 bytes or more ("ab*@xyz.co m" is 11 characters long).
Which criteria should I give more priority to in deciding which way to go?

Despite what I said above, sometimes it's worthwhile to duplicate data,
for instance if you export data in the referencing table without sharing
the data in the primary user_account table. An email address is easier
to browse through than an arbitrary integer. So it depends on how you
are going to use the data.

Regards,
Bill K.