My New Approach... Database Setup

PHP
1

Hi, my site allows users (buyers) to buy e-learning courses for other users (friends). The buyer completes a form along with choosing an e-learning course for their friend.

The one problem i have is that there are 8 different courses to choose from and some of them have additional settings. Because all of the courses are not the same (some have additional settings) i started off by creating a separate database table for each course type “course_type_1” etc along with the overall “course” table which holds all the main information about the course.

Now, i have realised that the only difference between each of the course types is that some courses have 1 or 2 additional fields SO i am now thinking that i can just add the following columns (in red) to the the overall “course” table and then delete the individual tables that i have created for each course type, so the “course” table would now look like the following:

COURSE (table)
id
buyer_id
friend_id
course_duration
course_type
additional_answer_one
additional_answer_two

I was then hoping to create a course_types.php file that would hold all of the set information for each course type and also determine if the form needs to show the additional text boxes for the additional settings. The course_types.php file would look something like the following:

----------- PLEASE SEE ATTACHED SCREENSHOT FOR A BETTER UNDERSTANDING

// declare courses and there variables

function course_type_1 ()
{
$course_name = Course One Name // this is what shows in the drop down menu
$additional_question_1 = What Is The Name Of The Book
$additional_answer_1 = $book_name
$additional_question_2 = What Is The Authors Gender
$additional_answer_2 = $author_gender
$background_img = canvas.png
$main_img = pencil.png
}

function course_type_2 ()
{
$course_name = Course Two Name // this is what shows in the drop down menu
$additional_question_1 = Please Choose A Color
$additional_answer_1 = $color
$additional_question_2 = NOT REQUIRED
$additional_answer_2 = NOT REQUIRED
$background_img = canvas.png
$main_img = pencil.png
}

etc

1 - what do you think of my new set up

2 - considering i have all of the course types listed in course_types.php - is this the best way for the form to call the Course Types into the drop down

Thanks in advance for your help, i am really looking forward to hearing your reviews on this, thanks…

2

Hmm if I understand what you’re saying… I would have three tables. Are you just using course type to ask different questions?

COURSE
-id
-name
-type
-duration
-etc

COURSE PURCHASE
-course_id
-user_id
-friend_id

QUESTIONS Having this as a separate table allows for infinite questions.
-id
-course_id
-question
-answer

3

Hi, yes course type is just used to ask the different questions…

I will only ever have 2 additional questions, so do i really need to create separate tables

Also, why can’t i place the user_id and friend_id in the course table

4

If you ever only have 2 questions it’s not necessary but good practice. If you ever changed your mind and decided you would need 3 or more questions and you had them all in the course table them you would have to go though a lot of effort changing not just the database table but you php code. If you had the question in a separate table and you decided to add more questions, it simply becomes a matter of adding another entry to the database, hassle-free. This way you are less likely to avoid problems in the future.

You should also keep you purchase info separate from your course table. Combining them means that only one user can purchase one course for one friends. Having a separate table like above allows for many users to purchase the one course for a friend, it also allows for one user to purchase the same course for many friends.

Good practice would be to say if it’s a different object or event it requires a different table.

So far you should really have the following tables:

Users
Friends (contains 2 user_ids to indicate they friends)
Courses
Questions
Purchases

5

Hi, thanks…

I am confused as to what you mean “Combining them means that only one user can purchase one course for one friends.”

That is not the case… remember a course is something that is created each time from the Form, so if a user (buyer) wants to pick the same course for another friend, he must build the course using the form and then a new record and id will be added to the course table. buyer_id and friend_id are then used to link the specific users… does this make sense to you :slight_smile:

6

Not really but just do what makes sense to you. :slight_smile: