I'm creating an web application in C# ASP.NET form school management in an state level.
The MASTER ADMIN registers the MUNICIPALITY ADMINS, the MUNICIPALITY ADMIN registers the SCHOOL ADMIN and in the end the SCHOOL ADMIN registers school ORIENTATIONS, SUBJECTS, CLASSES, TEACHERS and STUDENTS.
The application is mentioned to manage the primary and secondary schools.
Till now I registered all kind of users that are MASTER ADMIN, MUNICIPALITY ADMIN, SCHOOL ADMIN.
Also SCHOOLS, ORIENTATIONS, SUBJECTS and CLASSES.
All works good till now, but the problem exists while I'm trying to register TEASCHERS and STUDENTS.
The table PERSON has the following attributes:
CREATE TABLE [dbo].[PERSON](
[ID_Person] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](20) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[Sex] [nvarchar](10) NOT NULL,
[DateOfBirth] [date] NOT NULL,
[PlaceOfBirth] [nvarchar](30) NOT NULL,
[ID_Role] [int] NOT NULL,
[ID_Identification] [int] NOT NULL,
[ID_Municipality] [int] NOT NULL,
The table MUNICIPALITY
CREATE TABLE [dbo].[MUNICIPALITY](
[ID_Municipality] [int] IDENTITY(1,1) NOT NULL,
[Municipality] [nvarchar](20) NOT NULL,
The table SCHOOL
CREATE TABLE [dbo].[SCHOOL](
[ID_School] [int] IDENTITY(1,1) NOT NULL,
[SchoolName] [nvarchar](50) NOT NULL,
[AcademicYear] [nvarchar](15) NOT NULL,
[Address] [nvarchar](50) NOT NULL,
[ID_Person] [int] NOT NULL,
[ID_Municipality] [int] NOT NULL,
CONSTRAINT [PK_SCHOOL] PRIMARY KEY CLUSTERED
The table ORIENTATIO
CREATE TABLE [dbo].[ORIENTATION](
[ID_Orientation] [int] IDENTITY(1,1) NOT NULL,
[ID_School] [int] NOT NULL,
[OrientationName] [nvarchar](50) NULL,
CONSTRAINT [PK_ORIENTATION] PRIMARY KEY CLUSTERED
The Table IDENTIFICATION
CREATE TABLE [dbo].[IDENTIFICATION](
[ID_Identification] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](20) NOT NULL,
[Password] [nvarchar](20) NOT NULL,
[ID_Role] [int] NOT NULL,
[RegistrationDate] [date] NULL,
[Status] [bit] NULL,
CONSTRAINT [PK_IDENTIFICATION] PRIMARY KEY CLUSTERED
and table ROLE
CREATE TABLE [dbo].[ROLE](
[ID_Role] [int] IDENTITY(1,1) NOT NULL,
[RoleName] [nvarchar](30) NOT NULL,
CONSTRAINT [PK_ROLE] PRIMARY KEY CLUSTERED
Hopefully you are not confused on those tables, but when I try to register a teacher, it must have a reference from school like column ID_School from table SCHOOL, and the problem is that MASTER ADMIN and MUNICIPALITY ADMIN are not part of SCHOOL so if I add another column named ID_School in table PERSON all of users must belong to one school.
My question is:
How to add a TEACHER in my system and to refer to a specific school?
Do I have to add another table between tables SCHOOL and PERSON or how to solve this.
Thank you in advance for your patience while reading this topic.
Cheers.