Database Design and Input Form

[uniopp]

Hi,

I'm setting up a database of courses and related information (levels, times, fees, etc.) and I am looking for some design advice.

I want the user to be able to input the following -

Courses: 1 - 50 (anywhere from one course up to fifty courses).

Levels: 1 - 20 per Course.

Times: 1 - 10 per Level.

Fees: unlimited combinations for the above Courses/Levels/Times.

I know how to set up the relational tables but I am concerned about the number and layout of html forms that will be needed to collect the information. Unless I'm missing something the user will need to complete hundreds of fields or forms to cover every combination.

Could someone please explain how best to set this up so the users time is kept to a minimum?

Thank you.

Simon.

 

[uniopp]

I’ll try to expand a bit on my last post –

Let’s say that we have a table named SCHOOLS that contains general details about the school.

We want to allow each school to enter one or more courses, so we have a table named COURSES. (Each school can have one or many courses and each course can have one or many schools). Note: Most courses will only relate to one school but if the school has partner/branch schools they may share the same courses.

Each of the courses has up to 20 different entry levels (Beginner, Elementary, Intermediate, Advanced, etc.), so we have a table named LEVELS. (Each course can have one or many levels and each level can have one or many courses).


Each of the entry levels may have 10 hour, 15 hour, 20 hour, 25 hour per week time options so we have a table named TIMES. (Each level can have one or many times and each time can have one or many levels).


Each time option may have different start dates so we have a table named DATES. (Each time can have one or many dates and each date can have one or many times). Note: There may also be different dates for different levels so we need some relation there as well ??????????.

Each level, time and date may also have different pricing so we have a PRICE table. (this is where it starts to get real complicated ?????????????).

I welcome advice on any of the above but what I really can’t picture is the best html form layout to gather all of the details. To me it seems that the user (school) would have to complete a great number of fields spread out of 4 or 5 pages for EACH course they want to enter. Some schools may have 50 courses.
Any ideas?
Thank you.
Simon.

 

[tranquillo]

I don't get why the pricing should get complicated... the way I see it you just put a price in the dates table.

School(SchoolID primary key, name, whatever)
Course(CourseID primary key, SchoolID FK, name, whatever)
Level(LevelID primary key, CourseID FK, name, whatever)
Times[wich I'd prefere to call lenght or something like that](TimesID primary key, LevelID FK, lenght, whatever)
Dates(DatesID primary key, TimesID FK, startingDate, price, whatever)

why spread them out? maybe it'll look better, but the users would probably hate it anyway if it takse hours to use...

put it all in one page, and just group them logicaly...
maybe the school part should be on it's own page or filled in automaticly or something depending on the login system. if a user can be connected to a school then maybe the user shouldn't have to fill that in each time...

well... these are just some thoughts...

-s