Схема базы данных MySQL для двух реляционных таблиц

Структура таблицы

Создайте таблицы, подобные изображению, как показано, и используйте время кодирования/декодирования json для ввода/вывода. В запросе вы можете получить общее время из таблицы.

В простейшей форме, предполагая отношения не многие ко многим:

Course                Theme
--------              --------
CourseID <--+         ThemeId
Name        |         Name
            +------   CourseID
            |
            |      
            |         Video
            |         --------
            |         VideoID
            |         Name
            |         Length
            +------   CourseID
            |
            |
            |         Resource
            |         --------
            |         ResourceID
            |         Name
            +------   CourseID

В этой форме курс может иметь много тем, много видео и много ресурсов; но у каждой темы, видео и ресурса может быть только один курс.

Однако я не думаю, что вы этого хотите.

я бы больше склонялся к

                      Course             Theme
                      --------           --------
            +---->    CourseId    +--->  ThemeId
            |         Name        |      Name
            |         ThemeId ----+      
            |
            |      
            |         Video
            |         --------
            |         VideoID
            |         Name
            |         Length
            +------   CourseID
            |
            |
            |         Resource
            |         --------
            |         ResourceID
            |         Name
            +------   CourseID

Это позволяет курсу иметь только одну тему, но много видео и ресурсов. Это позволяет темам иметь более одного курса.

Но все равно не совсем подходит...

Это позволяет многим курсам использовать одну и ту же тему, а также иметь более одной темы:

                      Course         Course_Theme      Theme
                      --------       ------------      --------
            +---->    CourseId <----- CourseId   +-->  ThemeId
            |         Name            ThemeId ---+     Name
            |         ThemeId       
            |
            |      
            |         Video
            |         --------
            |         VideoID
            |         Name
            |         Length
            +------   CourseID
            |
            |
            |         Resource
            |         --------
            |         ResourceID
            |         Name
            +------   CourseID

В настоящее время каждый курс может иметь множество тем, видео и ресурсов. Каждая тема может иметь много курсов. Каждое видео и ресурс принадлежат курсу (т. е. могут иметь только один курс).

Если видео или ресурс может относиться к более чем одному курсу, вам придется расширить его так же, как я сделал с темами.

Согласно комментарию, все много ко многим. Заметьте, у меня нет прямой связи между темами и видео, а также между темами и ресурсами. Я не думаю, что они будут необходимы; вы должны быть в состоянии подобрать то, что вам нужно, проходя курсы.

          Course         Course_Theme        Theme
          --------       ------------        --------
+---->    CourseId <---- CourseId                   
|         Name           ThemeId ----------> ThemeId
|                                            Name
|
|                        Course_Video        Video
|                        ------------        --------
+----------------------  CourseId                      
|                        VideoId ----------> VideoId
|                                            Name         
|                                            Length             
|                                                         
|                        Course_Resource     Resource
|                        ---------------     --------     
+----------------------- CourseId                        
                         ResourceId -------> ResourceId   
                                             Name         
                                             Url, etc.    

Теперь о запросах. Хотя можно использовать агрегатные функции вместе с группировкой, я думаю, что гораздо разумнее сделать это проще и просто вытаскивать элементы по одному.

Themes per course
SELECT T.* 
FROM COURSE C
INNER JOIN COURSE_THEME CT ON CT.COURSEID=C.COURSEID
INNER JOIN THEME T ON CT.THEMEID=T.THEMEID 
WHERE {insert your search conditions on course}

or, if you know CourseId:

SELECT T.*
FROM THEME T
INNER JOIN COURSE_THEME CT ON T.THEMEID = CT.THEMEID
WHERE CT.COURSEID = ?


likewise,

Videos per course
SELECT V.*
FROM COURSE C
INNER JOIN COURSE_VIDEO CV ON CV.COURSEID=CV.COURSEID
INNER JOIN VIDEO ON CV.VIDEOID=V.VIDEOID
WHERE {insert your search conditions on course}

or, if you know the CourseId:

SELECT V.*
FROM VIDEO V
INNER JOIN COURSE_VIDEO CV ON CV.VIDEOID = V.VIDEOID
WHERE CV.COURSEID = ?

to select the sum of the video lengths per course,

SELECT SUM(LENGTH) AS TOTAL
FROM VIDEO
INNER JOIN COURSE_VIDEO CV ON CV.VIDEOID = V.VIDEOID
WHERE CV.COURSEID = ?
GROUP BY CV.COURSEID

Now, the tricky part is videos per theme.  I am making an assumption here:  the set of videos per theme is the same as the set of videos per course per theme.

The long way around:

SELECT V.*
FROM VIDEO V
INNER JOIN COURSE_VIDEO CV ON VIDEO.VIDEOID = CV.VIDEOID
INNER JOIN COURSE C ON COURSEID = CV.COURSEID
INNER JOIN COURSE_THEME CT ON C.COURSEID = CT.COURSEID
INNER JOIN THEME T ON CT.THEMEID = T.THEMEID
WHERE THEMEID = ?

Blech. You can cut out the middlemen:

SELECT V.*
FROM VIDEO V
INNER JOIN COURSE_VIDEO CV ON VIDEO.VIDEOID = CV.VIDEOID
INNER JOIN COURSE_THEME CT ON CV.COURSEID = CT.COURSEID
WHERE CT.THEMEID = ?

Когда ваши таблицы нормализованы, вы можете получить любую информацию из любой исходной точки, которую вы выберете. FWIW, ваш пример довольно сложный, поскольку все отношения многие ко многим.

Обновлять

Несмотря на то, что у меня были курсы в качестве корневых, даже когда темы являются корневыми, ничего не меняется:

          Theme          Course_Theme        Course
          --------       ------------        --------
+---->    ThemeId <----  ThemeId                   
|         Name           CourseId ---------> CourseId
|                                            Name
|
|                        Theme_Video         Video
|                        ------------        --------
+----------------------  ThemeId                      
|                        VideoId --------->  VideoId
|                                            Name         
|                                            Length             
|                                                         
|                        Theme_Resource      Resource
|                        --------------      --------     
+----------------------- ThemeId                        
                         ResourceId ------>  ResourceId   
                                             Name         
                                             Url, etc.    

В этой конфигурации курсы имеют видео и ресурсы через ThemeId, то есть:

SELECT V.*
FROM COURSE_THEME CT 
INNER JOIN VIDEO_THEME VT ON VT.THEMEID = CT.THEMEID
INNER JOIN VIDEO V ON V.VIDEOID = VT.VIDEOID
WHERE CT.THEMEID = ?

Создание схемы

Шаг 1. Определите объекты и их атрибуты

• Курс (ID, название, описание)
• Тема (ID, название, описание)
• Видео (ID, название, описание, продолжительность)
• Ресурс (ID, заголовок, URL)

Шаг 2: Определите отношения

• Тема => Курс
• Видео => Тема
• Ресурс => Тема

Шаг 3: Создайте таблицы

• курсы
  • ID (ПК)
  • заглавие
  • описание
• темы
  • ID (ПК)
  • id_курса (ФК)
  • заглавие
  • описание
• ролики
  • ID (ПК)
  • theme_id (ФК)
  • заглавие
  • описание
  • продолжительность
• ресурсы
  • ID (ПК)
  • theme_id (ФК)
  • заглавие
  • URL

Если темы может совместно использовать ролики и ресурсы, то это будут отношения многие ко многим. В этом случае вам понадобятся отдельные таблицы для этих отношений. Удалите столбец theme_id из videos и ressources и добавьте следующие таблицы:

• темы_видео
  • theme_id (ПК) (ФК)
  • video_id (ПК) (ФК)
• themes_ressources
  • theme_id (ПК) (ФК)
  • ressource_id (ПК) (ФК)

Здесь вы должны определить составной первичные ключи на (theme_id, video_id) и (theme_id, ressource_id). Также создайте обратные индексы для (video_id, theme_id) и (ressource_id, theme_id).

Получение данных

Предполагая, что вы знаете идентификатор курса (который равен 123), затем вы можете получить соответствующие данные (из схемы многие ко многим) со следующими запросами (которые вы выполняете один за другим):

select c.*
from courses c
where c.id = 123;

select t.*
from themes t
where t.course_id = 123;

select distinct v.*
from themes t
join themes_videos tv on tv.theme_id = t.id
join videos v on v.id = tv.video_id
where t.course_id = 123;

select distinct r.*
from themes t
join themes_ressources tr on tr.theme_id = t.id
join ressources r on r.id = tr.ressource_id
where t.course_id = 123;

Затем составьте свой массив/объект из полученных данных в PHP.

Представление

Попытка получить все данные с помощью одного запроса SQL не всегда является хорошей идеей. Вы просто делаете свой код и схему слишком сложными. Выполнение пары запросов — это не конец света. Чего следует избегать, так это выполнения запроса в цикле. (например: для каждой темы выберите соответствующие видео).