database design for creating a simple LMS prototype

Question

I have to create a Learning Management System. This is my first project of this type. As a simple test I am trying to develop a small prototype.

One of the feature here would be that the faculty members should be able to upload class materials chapter wise for different subjects for different classes. For example, teacher should be able to upload assignments for

Class 7 --> Subject: Science --> Chapter Name: Force --> Assignment 1

.

Note that different classes can have same subjects and same chapters also but the assignment numbers will be different.

So how can I design database for this task?

I've created one design but I do not know whether it will be correct approach or not?

CREATE TABLE ClassTbl (
    ClassID int,
    ClassName varchar(255) -- values 'one', 'two', 'three'...
);
CREATE TABLE SubjectTbl (
    SubjectID int,
    SubjectName varchar(255), -- values 'science', 'maths', 'grammar'...
	ClassID int
);
CREATE TABLE ChapterTbl (
    ChapterID int,
    ChapterName varchar(255), -- values 'trignometry', 'geometry', 'vector'...
	SubjectID int
);
CREATE TABLE AssignmentTbl (
    AssignmentID int,
    AssignmentFilePath varchar(255), 
	ChapterID int
);

Is this the correct approach or not?

Answer 1

Hi arsar,

Firstly, none of your tables have primary keys created. You should always have a way to uniquely identify and access a row. Before you dive into creating your tables and writing any code, you should initially dedicate time up front to develop a logical data model that attempts to achieve at least 3rd Normal Form.

You can start from this: SQL Server Database Design Best Practices Tutorial

There are many other sections in this link, such as best practices for creating clustered indexes, etc.

Hope this would give you some help.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".