Databases: Why moodle's DB dont have relationship and foreign Keys?

Databases: Why moodle's DB dont have relationship and foreign Keys?

by wendy solorzano -
Number of replies: 6

Sorry if I’ve made a mistake, I don’t speak English very well, but I tried to explain because I need your answer.

I doing my thesis about Moodle, and in the Data base’s design, I need to do the ER model, then I did reverse engineer with a program, the result was that the tables comes without foreign Keys and relationship with other tables.

Only comes with primary keys and index.

My question is: Why is the reason moodle’s data base don’t have relationship with other tables and foreign keys????

Average of ratings: -
In reply to wendy solorzano

Re: Databases: Why moodle's DB dont have relationship and foreign Keys?

by Martín Langhoff -
Hi Wendy. I think you had a reasonably good reply to this in the Spanish forum. smile
Average of ratings: -
In reply to Martín Langhoff

Re: Databases: Why moodle's DB dont have relationship and foreign Keys?

by wendy solorzano -

Thanks martin, your answer in Spanish was very good, but I had a doubt, is not very important that moodle's db doesn’t have a relationship and foreign keys???
My questions is, moodle don’t need relationship and foreign keys for the a correctly work??

Please if you can answer me in Spanish.

Average of ratings: -
In reply to wendy solorzano

Re: Databases: Why moodle's DB dont have relationship and foreign Keys?

by Martín Langhoff -
Moodle does have and use foreign keys. If you look in mdl_log table for instance, userid points to user.id, and courseid to course.id . But the DB doesn't really know they are FKs, and so it doesn't help us enforcing it.

It'd be better to have db-enforced FKs from a data sanity POV -- right now we are "enforcing" those in the application code, which is more work and far from perfect. However... Moodle's most popular platform is MySQL v4.x series using myISAM or ISAM, which doesn't support FKs.

Using MySQL with InnoDB (which supports FKs) is quite slow. At Catalyst we use PostgreSQL which does support FKs and it's not slow at all, but Pg users are the minority these days.

Until FK support is more widespread, I don't think it is practical. On the other hand, we could get ready for it -- it will take quite a few changes to core code to do things in an FK-sane way.
Average of ratings: -
In reply to Martín Langhoff

Re: Databases: Why moodle's DB dont have relationship and foreign Keys?

by Gonzalo Aro -

would happen if creating relationships, there would be a problem in the code of moodle?

i work in Oracle database

Average of ratings: -
In reply to Gonzalo Aro

Re: Databases: Why moodle's DB dont have relationship and foreign Keys?

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

I don't understand your question?

Here is a diagram of the primary/foreign key information based on a moodle install (this was generated directly from the code)

http://www.examulator.com/er/

Average of ratings: Useful (1)
In reply to wendy solorzano

Re: Databases: Why moodle's DB dont have relationship and foreign Keys?

by Rex Lorenzo -

Well, Moodle was developed before MySQL, and I believe other major DB systems (?), had support for foreign keys. So it had to be able to work without requiring them.

The XMLDB files do define foreign key relationships, so you should be able to build a map.

Also, there is a report in "Site administration > Development > XMLDB editor" called "Check foreign keys" that uses this information to find foreign key violations.

Average of ratings: -