Member Avatar for 54uydf

I have 2 places I'm stuck on with my DB design..
1**************************************************************************
how to translate ERD that looks like this into real tables?

my entity is "Course", if before student wants to take some course he must take few pre-courses then how should I model this?
tableCourse- id, name
tablePreCourses- courseId, preCourseId

and the other way around, if those are the tables, is it correct to draw the ERD like in that picture? or it should be 2 entities with 1-M connection?

2******************************************************************************
so, I have Students, which my program would categorize and give them a Status (good, has a warning, expeled..etc)
every student has only 1 status at any given moment, but they change them and can return to past statuses..like- good, bad, expelled, bad (return to school), good...etc
so I want to keep the hystory of their statuses.
so I have few options here and I don't know which is the right one!!
Hystory-studId,date,status
Status-statusName
Student-Id, name..

so I can make Hystory be the weak entity to Student cuz there's no hystory without the student, but I don't know if I even need the Status table, but it's a big deal in my program, so if I want to make a report of all the expelled students I'll have to do some crazy SQL with the Hystory table to get every student's current status, so maybe I should add a "current status" field to Student..so then I DO need the Status table or not? also don't know if I should make the "status" in Hystory a foreign key, or one of the primary keys..

another idea is to make the Hystory the relation table between Student and Status, cuz every student has many statuses, every status has many students..but the thing here is that, the same student can have the same status more than once, so I'll need 1 more key in Hystory (date). if I do it like this then I can't really add the "current status" to Student, can I?

buttom line I'm stuck in a maze!! any ideas to would be appreciated!

  • 3 Contributors
  • 7 Replies
  • 211 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by 54uydf

Dani AI

Generated

Good direction already: ’s idea to treat “pre-req” as a relationship between two Course rows and ’s Status + Status_History split are both the right patterns. Below are focused, practical clarifications and pitfalls to avoid so the design stays correct and maintainable.

For prerequisites

  • Implement a single junction table that holds the two course roles (parent / prerequisite). Enforce two foreign keys back to Course and a uniqueness constraint on the pair so duplicates are impossible. Add a simple check to forbid a row where a course is its own prerequisite. To prevent accidental circular chains (A → B → C → A), detect cycles at insert time with a recursive query (or validate periodically). If you need very fast “is X a prereq of Y?” checks, cache the transitive closure in a helper table rather than recomputing it on every request.

For student status history

  • Keep a small Status lookup table for Referential Integrity. Record each change in a Status_History table with a timestamp. Two common ways to get a student’s current status: mark one row in History as current (an is_current flag updated inside a transaction) or derive the current status by picking the latest timestamp. The former speeds queries but requires careful transactional updates; the latter is simpler and immutable.

Example queries (MySQL 8+): get each student’s current status using a window function

SELECT student_id, status_id, status_name
FROM (
  SELECT sh.student_id, sh.status_id, s.name AS status_name,
         ROW_NUMBER() OVER (PARTITION BY sh.student_id ORDER BY sh.changed_at DESC) rn
  FROM status_history sh
  JOIN status s ON sh.status_id = s.id
) t
WHERE rn = 1;

Operational tips

  • Index (student_id, changed_at) for fast lookups, store timestamps in UTC, and avoid relying on is_current without atomic updates (use transactions or triggers). If you frequently query only current status for reports, consider denormalizing a current_status_id on Student but keep Status_History as the immutable audit trail.

Recommended Answers

All 7 Replies

Member Avatar for tharkun

Hi,

I think you're right that the Pre-requesite is a 1 to many relationship on the Course table, but it needs to be implemented by another table as you specified:

tablePreCourses- courseId, preCourseId

As the history problem, I'd separate out the Status, as you can set up a Referential Integrity (RI) rule where the status is a foreign key to the Status table & MUST exist. The Status_History table then holds the Student_Id, Status & a column for however the history is discriminated (year, date, semester_id, etc). I would also use Status_History to hold the current status of the student too.

I hope this helps.

Student_thing.jpg 26.29 KB
Member Avatar for 54uydf


As the history problem, I'd separate out the Status, as you can set up a Referential Integrity (RI) rule where the status is a foreign key to the Status table & MUST exist. The Status_History table then holds the Student_Id, Status & a column for however the history is discriminated (year, date, semester_id, etc). I would also use Status_History to hold the current status of the student too.

I hope this helps.

WOW I didn't think of putting a current)status in the Hystory_Status table!!! thank u for the idea..I was thinking for so long about this that I can't get out of the box anymore lol..that's a great idea..I'll add it like a bool field current (yes /no)..should work I think..

alright..so 1 last thing tht bothers me now..is the Status table..I don't have any fields besides the name there..so it seems kinda silly to make a table with 1 field and 4 elements lol..but I guess it'll make things more simple..

btw which software do u use to draw the ERD? looks simple..unlike others..

Member Avatar for tharkun

I know the Status table looks simple. but you can use the table to enforce Referential Integrity [RI} (i.e. make the rule that the database will ONLY allow a Status_History row to be inserted if the status_id corresponds to an entry in the Status table). You would similarly put RI between the Status_History & the Student table, so rows can't be inserted UNLESS they correspond to an existing student.
I used a drawing package to draw the ERD (All those notations are my own).

Member Avatar for BitBlt

I would add one thing to the course/pre-req table structures...a second identifying relationship between Course and Pre-req. Here's the rationale: If you think about the "pre-req" as defining a relationship between two courses, one course might have many pre-requisites, and one course might be a pre-requisite for multiple courses. So if you break it down completely, you'd have:

Course---->PreReq<-----Course

So when the primary key migrates (assuming CourseId) then the primary key of PreReq after unifying and rolenaming becomes CourseId(ParentCourse), CourseId(ChildCourse). Then when you put it back in shape you wind up with

CREATE TABLE [Course] 
(
[CourseId] [int] NOT NULL ,
CONSTRAINT [PK_Course] PRIMARY KEY  CLUSTERED 
([CourseId])
) 

CREATE TABLE [PreReq] 
(
[ParentCourseId] [int] NOT NULL ,
[ChildCourseId] [int] NOT NULL ,
CONSTRAINT [PK_PreReq] PRIMARY KEY  CLUSTERED 
([ParentCourseId], [ChildCourseId]),
CONSTRAINT [FK_PreReq_CourseChild] FOREIGN KEY 
([ChildCourseId]) REFERENCES [Course] ([CourseId]),
CONSTRAINT [FK_PreReq_CourseParent] FOREIGN KEY 
([ParentCourseId]) REFERENCES [Course] ([CourseId])
)
GO

This gives you referential integrity, enforcement of the relationship and flexibility to have any course be either role in different situations.

Again, sorry for not having a picture...if this isn't clear, let me know and I'll put together a prettier one when I get to a machine with my tools.

Edited by BitBlt because: n/a
Member Avatar for 54uydf

I would add one thing to the course/pre-req table structures...a second identifying relationship between Course and Pre-req. Here's the rationale: If you think about the "pre-req" as defining a relationship between two courses, one course might have many pre-requisites, and one course might be a pre-requisite for multiple courses. So if you break it down completely, you'd have:

Course---->PreReq<-----Course

So when the primary key migrates (assuming CourseId) then the primary key of PreReq after unifying and rolenaming becomes CourseId(ParentCourse), CourseId(ChildCourse). Then when you put it back in shape you wind up with
Course PreReq
(--------) (-----------------------)
(CourseId)--+--->(CourseId(Parentcourse) )
( ) +--->(CourseId(ChildCourse) )
(--------) (-----------------------)

This gives you referential integrity, enforcement of the relationship and flexibility to have any course be either role in different situations.

Again, sorry for the ASCII model...if this isn't clear, let me know and I'll put together a prettier one when I get to a machine with my tools.

hahaha yes..the ASCII is awesome but hard to understand lol
after somethinking I came back to the decision to draw it like this
since you are right, every course has 1 to many precourses and every course can be a precourse to 1..m courses..
so it's like M to M relationship between the same table..so I think that drawing represents it right.
what I did with the DB tables was
tblCourse: courseId, name...
tblePreCourses: courseId, preCourseId
so both of the courseId fields in tblPreCourse are foreign keys to the tblCourse.courseId

commented: You beat me to it! Well done! +1
Member Avatar for BitBlt

Yes, you got it exactly. Sorry it took me so long to edit/correct my post above...you beat me to it! Good job!

Member Avatar for 54uydf

Yes, you got it exactly. Sorry it took me so long to edit/correct my post above...you beat me to it! Good job!

BitBlt you are great!! thank you for the help!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.