having problem with integrating the payment schema

If a "payment" can only be for one thing, you might add an extra couple of columns to hold either a trackID or a courseID to show what was being paid for.

If a payment might cover multiple things, you might need a "purchased items" table where each entry would have a "payID" to relate it to the "payment" table, and the rest would contain a reference to a course or track being paid for.

Please sir, i sill dont know how to go about doing it. even when i create a purchase items table i still dont know how to relate this data. the courses table is different while the track table is also different. a student can choose to study on a track which comprises of multiple courses (e.g. Graphics Design) or choose to study just a single course fro

how do i store the information on the payment table to relate if the student is paying for track or paying for courses, of which the payment info can also contain other payments outside this two.

Am really stuck here!. Please guide me on how to create this database. Thanks

CREATE TABLE IF NOT EXISTS student ( stud_id INT(30) NOT NULL, first_name VARCHAR (50) NOT NULL, other_names VARCHAR (100) NOT NULL, sex VARCHAR (10) NOT NULL, dob DATE NOT NULL, Religion VARCHAR (30) NOT NULL, address VARCHAR (100) NOT NULL, phone INT (11) NOT NULL, email VARCHAR (100) NOT NULL, PRIMARY KEY (stud_id) );

CREATE TABLE IF NOT EXISTS courses ( course_id INT(30) NOT NULL, course_name VARCHAR (50) NOT NULL, course_fee INT(30) NOT NULL, PRIMARY KEY (course_id) );

CREATE TABLE IF NOT EXISTS track ( track_id INT(30) NOT NULL, track_name VARCHAR (50) NOT NULL, PRIMARY KEY (track_id) );

CREATE TABLE IF NOT EXISTS track_courses ( ID int NOT NULL AUTO_INCREMENT, track_id INT(30) NOT NULL, course_id INT(30) NOT NULL, PRIMARY KEY (id) );

CREATE TABLE IF NOT EXISTS payment ( pay_id int NOT NULL AUTO_INCREMENT, stud_id INT(30) NOT NULL, category VARCHAR(100) NOT NULL, Amount INT(30) NOT NULL, Amount_paid INT(30) NOT NULL, due INT(30) NOT NULL, status INT(30) NOT NULL, timestamp TIMESTAMP NOT NULL, PRIMARY KEY (pay_id) );

This is what I was suggesting:

CREATE TABLE IF NOT EXISTS purchase_items
  ( ID        int NOT NULL AUTO_INCREMENT,
    pay_id    int NOT NULL REFERENCES payment (pay_id),
    course_id int REFERENCES payment (ID),
    track_id  int REFERENCES payment (ID)
  );

So for each payment, there would be a row in this table for every item that was being paid for with either a "course_id" or a "track_id" filled in (and the other would be NULL). Depending on what kind of database you are using, you might add a "check constraint`" to enforce that only one of those two values are used.

Yep i actually thought of this, But i wasnt sure if leaving a column null was a good solution. Secondly i was hoping to automatically setting the amount field to the price for a specific track or course when selected.

Can I still achieve this with this table?

The whole idea here is a database for students where:

1. A student can take on a single course,
2. A student can take on a track course (A track course is a group of course from the courses table e.g. Html, Css, Javascript can all be on a track called Front End Design)
3. The student will be charge higher if he/she is offering a track course and lower if taking a single course.

4. The student can choose to enroll for morethan one course at a time.

5. payment will be made based on type of enrollment (track or single course), and which track or course is the payment for.

since track is a multiple selection of different courses, if i combine the courses and the track table into products, will it still be possible to assign multiple courses to one track?

Looking at my database tables, I believe there is a way it can be refactor for better experience for the kind of app I am practicing on. This is my first personal project with php and sql and I feel like a loser having been stuck here for days.

From your explanation, This is what i was able to come up with. I combined the courses and the tracks together into a product table. Please have a review at it and let me know if this is ok.

CREATE TABLE IF NOT EXISTS products ( prodID INT(50) NOT NULL, product_name VARCHAR (100) NOT NULL, product_type VARCHAR (100) NOT NULL, PRIMARY KEY (prodID) );

CREATE TABLE IF NOT EXISTS teachers ( teacherID INT NOT NULL AUTO_INCREMENT, first_name VARCHAR (100) NOT NULL, last_name VARCHAR (100) NOT NULL, gender VARCHAR (100) NOT NULL, religion VARCHAR (100) NOT NULL, PRIMARY KEY (teacherID) );

CREATE TABLE IF NOT EXISTS course_info ( courseID INT(50) NOT NULL, course_fee INT (50) NOT NULL, teacherID INT(50) NOT NULL, PRIMARY KEY (courseID), INDEX (teacherID) );

CREATE TABLE IF NOT EXISTS track_courses ( ID INT NOT NULL AUTO_INCREMENT, trackID INT (50) NOT NULL, courseID INT(50) NOT NULL, PRIMARY KEY (ID) );

INSERT INTO teachers (first_name, last_name, gender, religion) VALUES ('Martins', 'Idiege', 'Male', 'Christain'), ('Andor', 'David', 'Male', 'Muslim'), ('Christopher', 'Ukwe', 'Male', 'Christain'), ('Philomina', 'Undie', 'Female', 'Christian'), ('Sandra', 'Ekom', 'Female', 'Muslim');

INSERT INTO products (prodID, product_name, product_type) VALUES (101, 'Diploma in Computing', 'track'), (102, 'Certificate in Computer Operation', 'track'), (103, 'Certificate in Computer Literacy', 'track'), (201, 'Microsoft', 'course'), (202, 'CorelDraw', 'course'), (203, 'Excel', 'course'), (204, 'Access', 'course'), (205, 'PowerPoint', 'course');

INSERT INTO course_info (courseID, course_fee, teacherID) VALUES (201, 20000, 1), (202, 10000, 2), (203, 30000, 3), (204, 20000, 4), (205, 50000, 5);

INSERT INTO track_courses (trackID, courseID) VALUES (101, 201), (101, 202), (101, 204), (102, 201), (102, 203), (102, 204);

Thanks for your great work here Steven Parker. With you help I have learned alot from this sql problem. and i have also found a course here on tree house to continue my learning journey "SQL Reporting by example". Really thanks for your help