I am still unclear on some of the databases relationships and perhaps i was hoping you had a diagram of these relationships so that i can continue to make modifications to the database so as to create a more efficeint way of adding users to certain courses aswell as only displaying those courses to the student that they are apart of. (big task)
I don't usually like to promote products in forums, but there is a good graphical tool that I have used to create database schemas called DeZign for Databases. With another package, ImportER MySQL, you can import information about database structure and relationships right from a MySQL server into DeZign for Databases. It is easy to generate documentation and a relationship diagram in html. Although I do not have the ImportER MySQL, I am very happy with the Dezign for Databases. To find out more, check out http://www.datanamic.com
Talking about really free UML-tools, Why not check:
http://www.objecteering.com/downloads.php
A really free personal edition of a UML-design-tool and/or 30-day-trials of the the professional or enterprise edition? (see the enclosed checklist, limited personal edition = green column) And if you download you get this message:
Objecteering/UML Personal Edition is licensed for non-commercial use and for evaluation only. Commercial use is prohibited. While using Objecteering/UML Personal Edition you are limited to Objecteering/UML Modeler including the Documentation Generator, in single user mode. Customization of documentation generation, code generation and all other functionalities available with Objecteering/UML Professional or Enterprise editions are disabled. Using Objecteering/UML Personal Edition you have access to our support service at mailto:support@objecteering.com, limited to three technical questions. If you would like to receive a free evaluation key for Objecteering/UML Professional or Enterprise editions, valid for a 30-day period, please fill in the appropiate download form at : http://www.objecteering.com/downloads_products_enterprise_edition.php If you would like to purchase a permanent license key for commercial use including maintenance services, please send an e-mail to mailto:inquiries@objecteering.com. We hope you enjoy using Objecteering/UML!
I was sitting on a train the other day going from Leeds to London with my trusty laptop and I thought
"ahh ha, I shall bring my mysql moodle databases into MS Access as ODBC attachments and use the Access relationship tool to draw some diagrams so I can better understand the relationships between tables"
As you can see I tryly lead a wild, crazy and generally rock'n roll lifestyle. So I get Access to be able to see the tables and I start to try to make the links. Duh, I just don't quite understand the thinking. It seems that the primary key is gnerally called ID and I had problems locating the foreign keys. I was expecting something like
Questions.questionid
as a primary key pointing to a column in the answers table as
answers.questionid
where answers.questionid was the matching foreign key.
This is the kind of naming approach I am used to and it has a hint of self documentation about it.
Am I missing something, did I mis interpret something, is there a general naming convention or am I barking up an incorrect spruce.
Marcus
Well, in general I like my code to read as English-like as possible, and data is almost always passed around as objects.
I like to refer to $question->id rather than $question->questionid. Similarly $module->course worked better for me than $module->courseid at the time, though probably if I was doing it again I might do this differently.
So, yes, almost every table has a unique "id" field, and foreign tables reference that field using the plain name of the table, usually.
An exception is userid, which had to be changed from "user" because it's a reserved word in non-MySQL databases. That was enough of a pain to change at the time - so I'm not anticipating any more changes for a while.
I like to refer to $question->id rather than $question->questionid. Similarly $module->course worked better for me than $module->courseid at the time, though probably if I was doing it again I might do this differently.
So, yes, almost every table has a unique "id" field, and foreign tables reference that field using the plain name of the table, usually.
An exception is userid, which had to be changed from "user" because it's a reserved word in non-MySQL databases. That was enough of a pain to change at the time - so I'm not anticipating any more changes for a while.