a college datastructure problem

I am in the design phase right now of a college datastructure that will manage the overall college information. Everything in the future application will be managed by departement. An instance of the application will manage one department at a time. For example, if for an instance of this application, Computer Science is chosen, within the Computer Science department, the following entities will be managed:Students,Courses,Teachers,Assignements... Other Students,Courses,Teachers,Assignments from the Engineering Department won't be visible within that same instance of the application.The future system will also manage Payroll for the Teachers. Since a Teacher will teach in different departments, how can I make sure that when they will enter a new teacher named Arnold Smith in the instance of the application that will manage the Computer Science Department, it is not duplicating the same Arnold Smith that also teaches in the Engineering Department?

If this a for-real application, you have a host of security issues (particularly if you are going to have salary info)! It is very difficult to provide good security for a database, and a reasonably secure version is usually a pain in the *** to administer and maintain. At a minimum, you would want to have a separate table for teachers and students, then refer to them by some unique identifier (the whole point to a relational database, after all) in the associations for each department. You can keep the rest of the data (one table for courses, another for assignmenets, etc.) in a single table, but then you must rely on application level security to protect the data (not a very good solution if you don't have control of the application (meaning each user has a local copy they can hack)). If you put the dept data in individual dept tables, you have messy code managing all the different departments and have to constantly update your code. There is no clean and easy solution even with a minimum of three tiers, one for the client (which has little or no security beyond authorizing and tracking individual users), a second tier on the server, then the database that can only be accessed through the server. You would then have to ensure that no one can get their hands on the server code (source or exe) and have a robust way of tracking individual logons. The least of your problems, it seems to me, is the actual database structure, but obviously your choices now will have a big impact on the overall application.

have a Person table, and have a DepartmentStaff table that links a person to a department. There can be multiple DepartmentStaff records that point to a person, each with a different department. Now, the interface is key. When they enter a person named "Arnold Smith", you have to know whether he's already in there, or whether it's just another person with the same name. You can do this by a FacultyIDNumber which is issued to every current and new employee, or by Social Security Number. That way when they try to enter Arnold Smith with ssn 123-45-6789 and he's already in there, it can stop them from doing so.

Unless the instances can talk to each other, such as by sharing a common master Person table, there's no way to make sure a person isn't duplicated.