Need advice on database plan. Thank You.

Hello,

I am working on a web site which will use SQL 2005.
I am planing my first SQL database and I am looking for advice.

1. There will be two types of users: students and professors.
2. Both users types will have login information.
(Username, Password, AccessLevel)
3. The remaining information on students and professores is different.
Student (Name, Email, Phone, ...) / Professor (Name, Email, Phone, Subjects, ...)
4. Professors can publish documents.
Each document has some info (Type, Title, Description, ...)

My plan in this moment is to:
A. Create the tables Students, Professors, Login and Documents.
B. Students table would be connected to Login table.
Professors table would be connected to Login table and Documents table.
C. The field [Type] in documents table should include the type or
should I create a table DocumentsTypes where I add codes for each type.
I have seen this. What is the advantage?

Can someone give me some advice?

Thank you Very Much,
Miguel

I would recommend you start with the aspnetdb sample database first. That will take care of your login, username, password, email, password retrieval, and roles.

Create the roles you need like student, professor, admin.

For part c which isn't really related to login/users, the difference is that while you can store the Type as a varchar, doing so is prone to accidentally entering an invalid entry. If you ever need to add another document type in the future, it is best to store all the values in a table (with a primary key, autoincrementing number is good for this), then store the key in the documents table. You can then put a foreign key constraint on the DocumentType field in the documents table so that it will only allow valid values in that field. Secondly, it will allow you to build your website so that you can populate a dropdown with the values the user can pick. Should you ever need to add another document type, just add it to the table (Directly or through another web page), and now your dropdowns will contain the new value, and the database will allow that value to be put into the DocumentType column automatically.

If you use the varchar type field, you will most likely put all possible values into the dropdowns in your code, and if you ever need to add another value, you will need to change your code, recompile the website and redeploy it again.

There is also a minor performance improvement with using the key as well since it will be an int field (4 bytes), verses a longer (and variable length) varchar field. Computers can change/compare int fields EXTREMELY quick. Computers look at strings as a sequence of chars (Or better algorythms may look at them as a sequence of ints, followed by some chars). In either case, varchars are many times slower.