What is the best way to design a database?

Question

MySQL

I'm making a small site for several groups at the university. a page with a schedule, a file archive, and other joys. There was a question about the daily schedule. on the page of each group will hang its schedule for tomorrow (if desired - for any day). at the moment, there are two tables, but they are not related to each other in any way:

subjects (list of subjects)
subjId (unique number of the subject)
subjName (full name of the subject)

groups (list of groups)
groupId (unique number of the subject)
subjName (group name )

it is necessary to match each individual group with a list of items for Monday\Tuesday\etc...
knowledge of php and mysql - at the initial level. therefore, I wanted to ask the guru how to design the database more successfully.

Answer 1

well, nothing complicated, the table will be something like this:
timetable{
subjId int
groupId int
day int
startTime time
}
daily schedule for the group:
select * from timetable,subjects where timetable.subjId=subjects.subjId and groupId= and day= order by startTime ;

Answer 2

What about even/odd weeks? Well, not right now - then they will appear.
What about the audience number? And the full name of the teacher ... After all, this add. there is already enough information to get a 4 on the exam in the subject. :)

Answer 3

do one-to-many in a table with groups.

Answer 4

Google about the ER-model (entity-relationship model), just your case. You can read more about normalization if you master the terms from the first run ...
You can google something more specific, for example, this (there, however, is an access, but you can look at pictures with the model).

Answer 5

We don't touch groups. A group can have multiple items. Let's make a table:
id (unique number)
id_group (group)
id_subjects (subject)
id_day (day of the week)
There may be other fields like lower/upper week.
To add an item to a group, add the necessary data to the table. The selection is simple, we select everything for the desired date for the desired group. Or we select everything by the desired date and make a schedule for all groups for this day of the week.

Answer 6

Better late than never :) eax.me/database-design