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...
Hi there. I'd start from beginning : you have some course (8) who differs for some details, and you'll have an undefined number of buyers. I'd make a table for buyers ( name,email,course, and so on.. ) and a table for the courses ( name of course, additional question true or false.....) with a one-to-many relationship. the form is only an input form, not a way to build data or table's row. An example : when you visit a web store, you choose from a fixed list of products. the database and the web page already contains articles. I'd suggest you to download any php e-commerce script and take a look inside, and you may find one where you just change a few things.
007ml is not saying that he will create table from an input form. He is exactly doing what you mentioned in your example. Yes, it is a good approach. But how good depends on other factors. Will there be more courses in the future or will it always be 8? How many columns in "course table"? How many additional information for each course? If there is going to be more courses then you should rethink your new approach. If the new courses are going to be very different and will need many additional columns and there will be many such new courses then it is better to switch back to your old approach because then the courses will be too different from each other to be placed under same table. If there are very few columns on course table any there are may other columns for additional information then in such case too this approach is not a good one. The number of columns in the course table should be more and that of additional information should be as less as possible. Lastly how many additional information would you need for each course. If there are many, then you better make them separate table. if there is few, 1, 2 or 3 then its ok. One thing that you should keep in mind that the table will be grow in size every time you add any new additional information column. So, at the end there is going to be many columns in your table which will make you maintenance task very complex. So, what you can do further than this is separate the main course table into 2. first main course table that will hold columns that will be common for all courses and second the table for additional information. That way your main table will not be too complex. The way you are using the form is good. create a set of variable for each course and load the form objects according to those variables.
Hi, thanks for your help guys... I will answer a few off your questions QZAX first: Will there be more courses in the future or will it always be 8? - it might stretch to a max of 14 courses but they are all very similar How many columns in "course table"? - there is 12 columns (which now includes course_type, additional_1 and additional_2) How many additional information for each course? some have 0 additional information, then others have either 1 or two (and there will never be a need for anymore than 2 additional information columns) @Tiberius14 - i already have two sperate tables (a 'users' table AND a 'courses' table). If you look at the database section in my screenshot you will see that there is a buyer id in my 'courses' table which links to the 'users' table @QZAX & @Tiberius14 - some people are telling me to split the additional questions and answers up into a separate table BUT there will never be anymore than 2 additional questions so i don't think i need all of this flexibility... what do you guys think
If you are NEVER going to have more than two additional questions then this is not a bad idea and will be OK as long as the number of courses remains small. However, for future, especiall if you are going to work on larger products would be better to have a seperate table for the questions. Put it this way, imagine if you had 20,000 courses and only two had the additional questions / answers ... if this were the case you have 39,998 unessary and empty database fields! If done correctly, you would have and extra table with the necessary additional rows. Furthermore, you can bet whatever you want that some day, the third question will appear!!!
Hi, thanks for your reply... yes but if i had 20000 courses, wouldn't the site run better if it only needed to query the one table for a small query instead of querying one and then linking to another another
I would suggest the original poster and ALL repliers get themselves familiar with Database Normalisation, this will answer all of your problems. If you can optimise your data to 3rd Normal Form this will solve all your problems. A Google search will quickly reveal the subject and its one that Wikipedia actually gets right (last time I checked of course!). Thanks Andrew
It is nice to see some bring up the standard way to do the task. But this is theoretical text book subject. Not everyone is familiar with the Normalization, Normal Forms. So, I suggest you to give a practical solution along with the theoretical standards when you reply to any question. Since you pointed out to all the repliers get familiar with Database Normalization and since I am one of the replier, I would like to ask where does my solution do not meet the Normal Forms that you are talking about in your Normalization. There is more I would like to tell awood969 and everyone else that Theories and Rules from textbook are not always the best way to do. Sometime even the theories contradict each other. Since awood969 has shown great importance of 3rd normal form, it is necessary to state that there are cases in the practical world when this is not desirable. Many times you have to break rules and theories to come up with unique solution to the huge problems and issue which the standard theories can not solve. For instance, when you go for Data Mining, often you may have to sacrifice these rules and the normal forms too. Sometimes you prefer and need DeNormalized form. And you can even go to 6th normal form, the question is how far do you need to go and what is best for you? I would also like to mention that I am a graduate on this field and I do have good deal of experience on working with large databases of big companies. Good suggestion by mallorcahp.
Qzax, thanks for your input again... so what is your final suggestion on this... my site cannot have a need for more than 2 questions as all of the courses are almost identical apart from the 2 questions. Would the site not run smoother if all this info was accessible from the one table, thanks again Qzax
On the basis of what I understood from you requirement, your system is not going to be too big. So, there will be no difference in performance whether you use one table or split it to two. What mallorcahp suggested becomes important when you need to work on large database. Like awood969 suggested it is better to go with proper technique but for small system even some defects will do any harm to the system. If it is a large system, the defect will multiply thousand of time and at the end it will become a very serious issue. Final words, you can go with single table. That will also simply the queries since you will not need to use joins. So, if you are not good with queries, that will ease your task by a lot.