Re: Designing a forum - general design logic question

[Kimo Maru]

Rick,
THANK YOU! This is exactly the kind of input I was looking for. The tables and entities are easy to understand, just need to figure out how to stitch them together.

The user table you suggested is interesting. I started off creating a user table in my design, but got hung up on how authentication would actually work. This is my create and input syntax that I put together;

CREATE TABLE users
(
UID SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(20) NOT NULL,
MiddleName VARCHAR(20) NULL,
LastName VARCHAR(20) NOT NULL,
DateOfBirth DATE NOT NULL,
Alias VARCHAR(20) NOT NULL UNIQUE,
Password VARCHAR(40) NOT NULL,
Email VARCHAR(40) NOT NULL UNIQUE
);

INSERT INTO users
(
FirstName,
MiddleName,
LastName,
DateOfBirth,
Alias,
Password,
Email)
VALUES
(
'Reed',
'Stretch',
'Richards',
'1961-11-01',
'mrfantastic',
SHA1('fantasticfour'),
'[e-mail]'
),
(
'Susan',
' ',
'Storm-Richards',
'1961-11-01',
'invisiblewoman',
SHA1('fantasticfour'),
'[e-mail]'
);

My understanding is that SH1 is probably one of the best ways to go (better than MD5, apparently). The part that hangs me up is what operation would allow the credentials entered so that they're compared with this user table and determine the user's access? My erroneous thinking was that by adding the accounts to the server's grant table that they would log in via the same mechanism (which, I'm sure, is the problem you stated. It occurred to me also that it would be insecure, that's why I mentioned it in my post).

Thanks, Rick. I appreciate the input.