MySQL Forums
Forum List  »  Newbie

Re: Learning Management System MySql Database design
Posted by: Rick James
Date: March 06, 2012 10:57AM

Let's assume you need just one database.

> the system flow are...
Instead, first focus on the "entities" you have.

One entity you have is a "student"
> 1)student that including junior/intermediate/senior student.
So, have a `Student` table.

But, first, ask whether these are essentially equivalent except for junior/etc? (I suspect they are.)

What other attributes are there of each "student"?
Possibly a `name`? That would be a VARCHAR.
Grade level (junior, etc)? That could be an ENUM.
Other fields (columns)?

Write out that CREATE TABLE and show us for critique.

Next entity:
> 2)instructor includes a full control administrator.
Again, let's tentatively make a table `Instructor`. What fields does it need?

Now you have two tables. Let's step back and look at them. Do they have the same columns? If so, then consider combining them into a single table. (I don't know enough about your application to know whether it is 'right' for your case.)

If they are combined, then we need a way to distinguish an "instructor" from a "student". There are multiple ways. One way is to add another field that is ENUM('student', 'instructor'). Another way is to add 'instructor' to the ENUM('junior', ...). But wait. Can an "instructor" also be a student (like a "teaching assistant")? If so this is not a good plan.

Etc. (videos, chat messages, ...)

Once you have laid out tables for the "entities", we need to talk about "relationships" between the entities. Learn about one-to-many and many-to-many mappings. For example, a chat message was written by exactly one user, so the user-id can (should) be a field in the ChatMessage table.

1:many relations are implemented by an extra column (as just mentioned).
Many:many relations are implemented by an extra table that has two (sometimes more) columns -- ids of the two entities that are being related.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.