Click here to Skip to main content
15,892,965 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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
SQL
CREATE TABLE [dbo].[MUNICIPALITY](
	[ID_Municipality] [int] IDENTITY(1,1) NOT NULL,
	[Municipality] [nvarchar](20) NOT NULL,

The table SCHOOL
SQL
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
SQL
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
SQL
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
SQL
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.
Posted

1 solution

I would add a TEACHER table, with a 1:1 relation to the PERSON table, and holding the field linking to a given school.
SQL
CREATE TABLE [dbo].[TEACHER](
	[ID_Teacher] [int] NOT NULL,
	[ID_School] [int] NOT NULL,
 CONSTRAINT [ID_Teacher] PRIMARY KEY CLUSTERED

ID_Teacher having itself a foreign key relation with ID_Person column of PERSON table.

Hope this helps.
 
Share this answer
 
Comments
dr_iton 24-Nov-15 12:39pm    
Yeah, that was a solution. Thank you very much mate.
Q: Why do I always find a solution here?
A: Because here are the best programmers in the world.
Cheers.
phil.o 24-Nov-15 12:54pm    
You're welcome :) Glad to have helped.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900