Need advice on database planning. 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,
MiguelYou should be using ASP.NET 2.0 as it now supports SQL providers for
Membership, Roles, and Profiles. Most of it is all automated and you won't
have to worry about the design in the context you are discussing at the
moment.
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ODVd5NmAGHA.204@.TK2MSFTNGP15.phx.gbl...
> 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
>

Need Advice

I just want to ask for any good advice here ragarding my project. I'm planning to create a program that will use an sql server as a database. I have 3 branches and I need the branches to be updated always when they create any transaction or changes. The sql server will be installed in a PC with Windows Server 2003 and with a public IP. the question is can I access the sql server from other branches by using the public IP of the ServerPC thru the Internet? If yes, how? if no, is there a best way to do this updating method?

Thanks!

It is not clear if you are considering that each branch will have a SQL Server and need to transfer data changes to the other branches, or if there is a 'central' SQL Server -with or without branch servers. That makes a 'big difference' with the kind of opinions you will recieve...

The 'best' method is Replication. That is what it is designed to handle. However, Replication between the branch SQL Servers is NOT included with SQL 2005 Express. (If you are using Express.)

Otherwise, you need to plan for service disruptions. What happens if the connection cannot be made?

Will you have a Queueing table that holds changes until their is confirmation they were recieved at all the branches?

IF you have a central SQL Server, and you expose it with a public IP, you will have every hacker in the world (literally) constantly attemping to get in. You MUST have a iron tight security plan and you will still be vulnerable.

|||I am planning to put a sql server for all branches where they will store per Branch Transaction so that I will not have a problem when there is no Internet. If ever there online already, the program will then connect to the central sql server to update the branches transaction. is that possible? how can i connect to the central sql server thru the internet?

Thanks for the advice.
|||If all machines are accessible to each other (e.g. you can ping, or telnet), then it's easy to connect to the central sql server(or each other). Just enable TCP on your central sql server and use TCP to connect from other machine. You can use DNS name of the server machine to connect. You may not be able to use Windows Authentication. I assume you will use some form of firewall to protect your system. You can generate a rule to only allow connections from your client machines.|||

In order to handle 'network outages', Transactional Replication would most likely be the preferred option. (Not available with SQL Express.)

And, as Xinwei indicates, you will have to open and protect the firewall for the appropriate IP ports.