How to design database for each year

Question

Sorry I can't post, so I had to edit my one of post

I'm working on a school management system, I have developed a database and it consists of 8 tables, Registration, family, parents, teacher, classes, subjects, grades, time_table, all these tables are related to each other and works fine until now. I want to add a table where I can store students class like grade 1, grade 2, grade 3 or (Class grade), and every grade should be returned on a separate table when I write the PHP script like grade 1 in the first table, grade 2 in the second table and so on. For more understanding lets assume Mike enrolled from first grade until 12 grade in one school, now mike should have 12 separate tables in his account only for grades. Hope it's clear );

-- MariaDB dump 10.17  Distrib 10.4.11-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: thesis
-- ------------------------------------------------------
-- Server version   10.4.11-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `classes`
--

DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_grade` char(2) NOT NULL,
  `class_code` varchar(50) NOT NULL,
  `class_name` varchar(50) NOT NULL,
  PRIMARY KEY (`class_id`),
  UNIQUE KEY `class_grade` (`class_grade`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `classes`
--

LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (1,'1','C1C','Class H'),(2,'2','C2K','Class H'),(4,'4','C4P','Class II'),(5,'5','C5K','Class L'),(6,'6','C6P','Class Z'),(7,'7','C7P','Class VV'),(8,'8','C8P','Class Z'),(9,'9','C9P','Class A'),(10,'10','C10P','Class M'),(11,'11','C11N','Class B'),(12,'12','C12N','Class A');
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `family`
--

DROP TABLE IF EXISTS `family`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `family` (
  `family_id_auto` int(11) NOT NULL AUTO_INCREMENT,
  `family_id` char(5) CHARACTER SET utf8mb4 NOT NULL,
  `NIM` bigint(14) NOT NULL,
  `family_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  `family_job` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`family_id_auto`),
  KEY `NIM` (`NIM`),
  CONSTRAINT `family_ibfk_1` FOREIGN KEY (`NIM`) REFERENCES `student` (`NIM`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `family`
--

LOCK TABLES `family` WRITE;
/*!40000 ALTER TABLE `family` DISABLE KEYS */;
INSERT INTO `family` VALUES (5,'23422',11160930000111,'asdf','sdf'),(6,'23423',31730771550040,'asdf','asdf'),(7,'11160',11160930000113,'Hi','Hello');
/*!40000 ALTER TABLE `family` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `grades`
--

DROP TABLE IF EXISTS `grades`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `grades` (
  `grade_id` int(11) NOT NULL AUTO_INCREMENT,
  `grade_number` float NOT NULL,
  `grade_string` char(1) DEFAULT NULL,
  `NIM` bigint(14) NOT NULL,
  `Teacher_ID` bigint(14) NOT NULL,
  `subjectid` char(5) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`grade_id`),
  KEY `NIM` (`NIM`),
  KEY `Teacher_ID` (`Teacher_ID`),
  KEY `subjectid` (`subjectid`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`NIM`) REFERENCES `student` (`NIM`),
  CONSTRAINT `grades_ibfk_2` FOREIGN KEY (`Teacher_ID`) REFERENCES `teacher` (`Teacher_ID`),
  CONSTRAINT `grades_ibfk_3` FOREIGN KEY (`subjectid`) REFERENCES `subjects` (`subjectid`),
  CONSTRAINT `grades_ibfk_4` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `grades`
--

LOCK TABLES `grades` WRITE;
/*!40000 ALTER TABLE `grades` DISABLE KEYS */;
INSERT INTO `grades` VALUES (16,100,'A',11160930000111,9876542345234,'29203',12),(17,90,'A',11160930000111,72093475893453,'56239',12),(18,50,'D',11160930000113,34512345123434,'29203',11),(19,60,'C',11160930000111,234324523452345,'72334',12),(20,90,'A',11160930000111,234324523452345,'63494',12);
/*!40000 ALTER TABLE `grades` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `login`
--

DROP TABLE IF EXISTS `login`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `login` (
  `admin_id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_nim` bigint(14) NOT NULL,
  `admin_name` varchar(50) NOT NULL,
  `admin_email` varchar(50) NOT NULL,
  `admin_pass` text NOT NULL,
  `admin_conpass` varchar(50) DEFAULT NULL,
  `admin_image` blob DEFAULT NULL,
  `admin_level` varchar(50) NOT NULL DEFAULT 'admin',
  PRIMARY KEY (`admin_id`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `login`
--

LOCK TABLES `login` WRITE;
/*!40000 ALTER TABLE `login` DISABLE KEYS */;
INSERT INTO `login` VALUES (68,11160930000120,'Saboor','saboorhamedi49@gmail.com','$2y$10$.LuuXYtEALQ1.cwL7nuGvO8D4HCHp8mfbcL4Kf4.jFHfRAItb6O/C','saboor','1591627412_IMG_0066.JPG','admin');
/*!40000 ALTER TABLE `login` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `parents`
--

DROP TABLE IF EXISTS `parents`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `parents` (
  `parent_id` int(11) NOT NULL AUTO_INCREMENT,
  `family_id_auto` int(11) NOT NULL,
  `NIM` bigint(14) NOT NULL,
  PRIMARY KEY (`parent_id`),
  KEY `NIM` (`NIM`),
  KEY `family_id_auto` (`family_id_auto`),
  CONSTRAINT `parents_ibfk_2` FOREIGN KEY (`NIM`) REFERENCES `student` (`NIM`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `parents_ibfk_3` FOREIGN KEY (`family_id_auto`) REFERENCES `family` (`family_id_auto`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `parents`
--

LOCK TABLES `parents` WRITE;
/*!40000 ALTER TABLE `parents` DISABLE KEYS */;
INSERT INTO `parents` VALUES (1,5,11160930000111),(2,6,31730771550040),(3,7,11160930000113),(4,5,11160930000111);
/*!40000 ALTER TABLE `parents` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `professions`
--

DROP TABLE IF EXISTS `professions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `professions` (
  `profession_id` int(11) NOT NULL AUTO_INCREMENT,
  `profession_name` varchar(250) NOT NULL,
  PRIMARY KEY (`profession_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `professions`
--

LOCK TABLES `professions` WRITE;
/*!40000 ALTER TABLE `professions` DISABLE KEYS */;
INSERT INTO `professions` VALUES (1,'Analysis'),(2,'Developer'),(3,'Adaptability'),(4,'Confidence'),(5,'Communication'),(6,'Team Player'),(7,'Continuous Learner'),(8,'Imaginative'),(9,'Innovative'),(10,'Commitment'),(11,'Ability to Manage Online Reputation'),(12,'Ability to Engage');
/*!40000 ALTER TABLE `professions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `NIM` bigint(14) NOT NULL,
  `name` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `nationality` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `age` char(3) NOT NULL,
  `class_id` int(11) NOT NULL,
  `studentpassword` varchar(50) NOT NULL,
  `student_level` varchar(50) NOT NULL DEFAULT 'student',
  PRIMARY KEY (`NIM`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (11160930000111,'Saboor','lastname','Indonesia','Afghan','saboorhamedi49@gmail.com','26',12,'123','student'),(11160930000113,'Ahamad','Mahmmad','Saudi','Arab','ahmadmahmmod893@gmail.com','15',11,'ahmad','student'),(31730771550040,'asdf','asdf','asdf','adsf','adfasdf','23',2,'','');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `subjects`
--

DROP TABLE IF EXISTS `subjects`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `subjects` (
  `subjectid` char(5) NOT NULL,
  `sub_code` varchar(50) NOT NULL,
  `subjectname` varchar(50) NOT NULL,
  PRIMARY KEY (`subjectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `subjects`
--

LOCK TABLES `subjects` WRITE;
/*!40000 ALTER TABLE `subjects` DISABLE KEYS */;
INSERT INTO `subjects` VALUES ('11111','AAAP','Architecture and Allied Arts'),('29203','ANTH','Anthropology'),('55555','E1GL','Englihs'),('56239','ACTG','Accounting'),('63494','AEIS','Applied Information Management'),('72334','AAD','Arts and Administration');
/*!40000 ALTER TABLE `subjects` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teacher`
--

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
  `Teacher_ID` bigint(20) NOT NULL,
  `Teacher_name` varchar(50) NOT NULL,
  `Teacher_Last_Name` varchar(50) NOT NULL,
  `Teacher_Address` varchar(50) NOT NULL,
  `Teacher_Email` varchar(50) NOT NULL,
  `Teacher_pass` text NOT NULL,
  `Teacher_Profession` varchar(50) NOT NULL,
  `teacher_level` varchar(50) NOT NULL DEFAULT 'teacher',
  PRIMARY KEY (`Teacher_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teacher`
--

LOCK TABLES `teacher` WRITE;
/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
INSERT INTO `teacher` VALUES (9876542345234,'Update','asdfasdf','asdfasdf','asdfa@yahoo.com','123','123',''),(34512345123434,'asdfasdfasdfasd','asdfasdf','asdfasdf','asdfasdf@gmail.com','123','Ability to Manage Online Reputation ',''),(72093475893453,'Fatih','Khan','Kabu/Afghanistan','hhdd@gmail.com','123','Analysis',''),(75623274291473,'Ahmad','Mahmmod','Mahmmod','Mahmmod','123','Developer',''),(234324523452345,'asdfasdf','asdfasdf','asdfasdf','asdfasdf@gmail.com','123','Communication ','');
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `time_table`
--

DROP TABLE IF EXISTS `time_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_table` (
  `time_table_id` int(11) NOT NULL AUTO_INCREMENT,
  `NIM` bigint(14) NOT NULL,
  `subjectid` char(5) NOT NULL,
  `Teacher_ID` bigint(14) NOT NULL,
  `class_id` int(11) NOT NULL,
  `day_time` varchar(15) NOT NULL,
  `start_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `end_time` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`time_table_id`),
  KEY `NIM` (`NIM`),
  KEY `subjectid` (`subjectid`),
  KEY `Teacher_ID` (`Teacher_ID`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `time_table_ibfk_1` FOREIGN KEY (`NIM`) REFERENCES `student` (`NIM`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `time_table_ibfk_2` FOREIGN KEY (`subjectid`) REFERENCES `subjects` (`subjectid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `time_table_ibfk_3` FOREIGN KEY (`Teacher_ID`) REFERENCES `teacher` (`Teacher_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `time_table_ibfk_4` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `time_table`
--

LOCK TABLES `time_table` WRITE;
/*!40000 ALTER TABLE `time_table` DISABLE KEYS */;
INSERT INTO `time_table` VALUES (1,11160930000111,'29203',9876542345234,12,'Monday','2020-06-04 16:04:00','2020-06-04 16:04:00'),(2,11160930000113,'29203',34512345123434,11,'Tuesday','2020-06-04 16:09:00','2020-06-04 16:09:00'),(3,11160930000111,'72334',234324523452345,12,'Thursday','2020-06-04 16:17:00','2020-06-04 16:17:00'),(4,11160930000111,'56239',72093475893453,12,'Thursday','2020-06-04 16:17:00','2020-06-04 16:17:00'),(5,506278287917,'11111',75623274291473,11,'Thursday','2020-06-04 16:19:00','2020-06-04 16:19:00'),(6,11160930000111,'63494',234324523452345,12,'Monday','2020-06-10 01:57:00','2020-06-10 04:57:00'),(7,31730771550040,'11111',9876542345234,12,'Tuesday','2020-06-11 00:30:00','2020-06-11 14:30:00');
/*!40000 ALTER TABLE `time_table` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `uploading`
--

DROP TABLE IF EXISTS `uploading`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `uploading` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `file_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `uploading`
--

LOCK TABLES `uploading` WRITE;
/*!40000 ALTER TABLE `uploading` DISABLE KEYS */;
/*!40000 ALTER TABLE `uploading` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-06-12 14:16:29

You have to save that information somewhere (in a config file for example). — Jean-Baptiste Yunès, Jan 27 '18 at 11:10
Possible duplicate of [How do I save preference user settings in Java?](https://stackoverflow.com/questions/4017137/how-do-i-save-preference-user-settings-in-java) — Romulo, Jan 27 '18 at 11:19
Sorry I really don't understand this, do you have any link about how to save image with Properties? — Saboor Hamedi, Jan 27 '18 at 11:27
You may need to rethink the way your app work. If you need to save not only the reference to the file but its content, then create some directory to store every thing needed by your app. — Jean-Baptiste Yunès, Jan 27 '18 at 11:55
This is the big problem, i have no idea how to make directory for that — Saboor Hamedi, Jan 27 '18 at 12:00

How to design database for each year

0 Answers0