** Using WeBWorK **

Another problem is that creating or deleting WWK courses requires root access to the MySQL server. This is because WWK creates a new SQL database for every course. It would be safer to create a single SQL database for all the webwork courses and then create different tables for different courses by prefixing the courseid to the current table names. This would allow the user webworkWrite to add/delete courses.

Zig

<| Post or View Comments |>

However, my second point stands.

<| Post or View Comments |>

Does anyone know of any disadvantages to using one database for all webwork courses and using course name prefixes on the tables?

--Mike

<| Post or View Comments |>

I've just finished hacking the WWK2 code to use this one database with course prefixes for tables approach. It only requires minor modifications to DB/Driver/SQL.pm and DB/Schema/SQL.pm, as well as somewhat more extensive modifications to Utils/CourseManagement.pm [The major modification in the last is an extensive rewrite of deleteCourseSQL().] I haven't tested it extensively, but the basic functions seem to be working correctly. [I don't understand the multiple sources stuff in addCourseSQL() and deleteCourseSQL().]

I think it is fair to say that Martin Dougiamas and Eloy Lafuente, the lead developers of Moodle, are very knowledgable about SQL. So I am fairly certain that there are no crushing disadvantages to the one database many tables approach. There are currently some very large Moodle installations - there is one in England with some 17000 users. The moodle.org site has some 2400 very active users (and steadily growing). On the other hand, one of their considerations for their Moodle database design is that it be capable of running in a shared hosting arrangement, which would not accomodate the WeBWorK many databases setup.

I will ask them for their thoughts on the matter.

Zig

<| Post or View Comments |>

You can see the discussion on this topic that I started on the moodle.org forum at the following URL:

http://moodle.org/mod/forum/discuss.php?d=10762&username=guest

[Drop the username=guest parameter in the URL if you want to participate in the discussion.]

Martin and another poster (who also claims extensive MySQL experience) see no efficiency benefits in the many databases approach and agree with me that there are significant security issues with it.

Zig

<| Post or View Comments |>

Here's a link to my SQL modifications for WeBWorK2

https://webwork2.math.ohio-state.edu/WW2SQLmods.tar.gz

Zig

PS. I should add that this code assume that the unique WeBWorK database has already been created and that it has no underscores in its name. Also that the users webworkRead and webworkWrite (or whatever names they have in database.conf) have been created and that the read user has SELECT privileges on this database and the write user has SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP privileges for this database. (Perhaps ALTER is not necessary.) In database.conf the entries for the "source" fields in dbLayouts{sql} should be "dbi:mysql:#####_$courseName" where ##### is the SQL database name.

<| Post or View Comments |>

I also notice that WeBWorK2 makes very little use of indexing in its SQL tables. A cursory look at the table structure seems to indicate that the only table which uses indexing is set_user, which is indexed on the psvn field. Surely more use of indexing would improve the speed?

Zig

<| Post or View Comments |>

I think changes to indexing and database/table structure were coming. One option on the tables is what you did. Another was to have one database with the standard tables, but to add course as a field in each. This cuts down on the number of tables mysql would have open at a given time.

You will definitely want keys/indexing for tables if you have a large tables. I think your other post mentioned 1000 students in a course. Assigning the third or even the second problem set would take quite a long time. The alternative table structure mentioned in the first paragraph would also make it essential since it would lead to very big tables.

John

<| Post or View Comments |>

It seems to me that the current situation, i.e. encouraging WeBWorK installations to upgrade to WeBWorK2+SQL while the basic structure of the SQL DB layout is still open, will lead to future compatibility nightmares. Although I suppose one could use the export/import database facility, this will be tedious and error-prone. One would require users to export all their old databases with an older version of WeBWorK before upgrading to the newer version and reimporting. I don't think this will be very pleasant prospect for WeBWorK users.

I think that your alternative database design proposal will lead to truly humongous tables, unless you have a policy of deleting old course from previous academic terms. In our current WWK 1.9 setup we have some 90 courses from years 2000-2004. Assuming 100 students per course, 10 problem sets with 10 problems each, we wind up with the problem_user table having some 900,000 records and steadily growing.

With my proposal, you get much smaller tables. Although there are many tables in the database, you would expect only a few of them to be actively read/written to. I think in our situation, one would expect students from maybe 5 (10 tops) courses to be logged in at any given time, and only 2 tables per course, problem_user and set_user, to be in frequent use. Also I think my proposal is easier to implement with the current codebase -- indeed I've basically implemented it already.

Zig

<| Post or View Comments |>

Thank you for your contribution to the WeBWorK project. We were already aware that the current system of using a separate database for each course is suboptimal. Your solution of using a separate group of tables in the same database for each course is excellent, assuming the current structure of the database. However, this structure is transitional, designed to allow a common API for both SQL- and DBM-based courses.

Sometime in the near future, we will be redesigning the database structure completely, to better satisfy current and future needs. During this process, we will evaluate the best way to represent "courses" within a relational model. We may go with a one-database-many-tables approach, or with some other approach.

See the WeBWorK Wiki topic DatabaseRedesign for more information. As the design process progresses, we'd appreciate your input.

We would like to add your database modifications to WeBWorK 2.1. It would help us if you could submit your changes as a patch file in unified diff format by adding a "feature request" to our bugzilla database and attaching the patch file to it.

As for upgrading current courses, it seems to me that it would be possible for per-course database courses and multi-course database courses to coexist on the same system. One would only have to "upgrade" courses if desired, and a utility could be written that would do the job in a way that would not be "tedious" or "error-prone".

Thanks again for your contribution.
-sam

<| Post or View Comments |>