CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
sonam
sonam

Reputation: 3760

correct db structure for subjects management in school management system

I have following db structure for a school information system:

enter image description here

With this db structure, I have to enter subjects of all levels (class1 ,class2 etc) for each academic year. Subjects of class 1 will remain the same for most of the academic years, (for eg: class1 subjects will be same for 2010,2011,2012). So the data entry personal will find this cumbersome to enter the subjects for each level per academic year even though the subjects remains the same.

There is a possibility that the course may change for each level at some interval of time and subjects for various level may change.

How can I structure my DB such that the data entry personal does not have to enter subjects every academic year and if course does not change and make necessary change when subjects change for certain level? For eg: class 1 subjects have not changed for 2013 so data entry personal does not have to enter subjects again. class 2 subjects changed for 2013 and data entry personal enter subjects.

Also suggest if the current structure suits the best.

Upvotes: 1

Views: 1078

Answers (3)

Neville Kuyt
Neville Kuyt

Reputation: 29619

The simplest solution is to change the table tbl_subjects to have "from_year" and "to_year" columns. A class is taught at a given level at a given point in time if there's a record where "from_year" smaller than date, and "to_year" is greater than the date, OR "to_year" is null.

Upvotes: 0

NoChance
NoChance

Reputation: 5752

Logical structure correctness should prevail over usage convenience. Since your database design fits the logical business requirements for the software to function correctly, data entry convinience could be handled thorough provided an easy to use GUI where the common information is provided as a default for user to approve or update (instead of entering it from scratch) or via a setup program that runs part of the configuration process.

In your particular case, this process is not a daily routine. It is probably executed at year start or first use of the software, so I suggest you don't change the database design if it is correct for your business.

Upvotes: 1

DoNotArrestMe
DoNotArrestMe

Reputation: 1279

If I understand correctly then first idea is:

  1. Delete table tbl_academic_years;
  2. Create new table tbl_subjects for subjects;
  3. Create new table tbl_relations.

Select:

After that if you need to select actual information - all you need is select from relation table with deleted = 0.

SELECT
    l.name,
    s.name
FROM
    tbl_relations r
    INNER JOIN tbl_levels l
    ON r.level_id = l.id
    INNER JOIN tbl_subjects s
    ON r.subject_id = s.id
WHERE
    r.deleted = 0

Add:

If you need to add new subject for levels first of all add new subject to tbl_subjects, then add record with new id subject to tbl_relations.

Delete:

If you need to delete subjects for levels - set value 0 to field deleted or delete rows in tbl_relations.

If subjects not changed - do nothing.

I think the best structure for your database will be the next script generated in mysql:

CREATE TABLE `tbl_levels` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255),
    PRIMARY KEY (`id`)
);

CREATE TABLE `tbl_subjects` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255),
    PRIMARY KEY (`id`)
);

CREATE TABLE `tbl_relations` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `level_id` int(11),
    `subject_id` int(11),
    `deleted` tinyint(1) DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `level_id` (`level_id`),
    KEY `subject_id` (`subject_id`),
    CONSTRAINT `tbl_relations_ibfk_1` FOREIGN KEY (`level_id`) REFERENCES `tbl_levels` (`id`),
    CONSTRAINT `tbl_relations_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `tbl_subjects` (`id`)
);

Upvotes: 2

Related Questions