Need help with designing good database models.

Hi, I’m new to Django and Web development in general. This is my first time trying web development.

I am enjoying Django so far and I am trying to create what I have been looking for a while and could not find. A self hosted course video organizer, something like Jellyfin but for course videos like a video from FreeCodeCamp.

I first though of creating models in a way that represents a TV show in Jellyfin. For example a directory will represent a series or a course, sub-directories (if present) will represent seasons of a series or chapters in a course and video files inside these sub-directories will represent episodes or topics in a chapter. And if there is only a single video file that will be taken as course as well with no directories.

I am looking for suggestions, tips and feedback on how should I design the models.

So far I came up with this,

from django.db import models
from django.utils import timezone
from django.contrib.auth.models import User
from django.core.validators import RegexValidator, FileExtensionValidator
from django.urls import reverse

from taggit.managers import TaggableManager

class Course(models.Model):
    title = models.CharField(max_length=150)
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    description = models.TextField()
    tags = TaggableManager()
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('course-detail', kwargs={'pk': self.pk})

class Chapter(models.Model):
    course = models.ForeignKey(Course, on_delete=models.CASCADE, related_name='chapters')
    title = models.CharField(max_length=255)
    order = models.IntegerField(default=0)
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('videos-detail', kwargs={'pk': self.pk})

class Videos(models.Model):
    course = models.ForeignKey(Course, on_delete=models.CASCADE, related_name='videos')
    chapter = models.ForeignKey(Chapter, on_delete=models.CASCADE, related_name='videos', null=True, blank=True)
    title = models.CharField(max_length=150)
    video = models.FileField(upload_to='videos',null=True, validators=[
        FileExtensionValidator(allowed_extensions=[
            'MOV',
            'avi',
            'mp4',
            'webm',
            'mkv'
        ])
    ])
    thumbnail = models.ImageField(upload_to='thumbnails')
    description = models.TextField()
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('videos-detail', kwargs={'pk': self.pk})

And here is the views.py for this app,

from django.shortcuts import render, get_object_or_404
from django.contrib.auth.mixins import LoginRequiredMixin, UserPassesTestMixin
from django.contrib.auth.models import User
from django.views.generic import ListView, DetailView, CreateView, UpdateView, DeleteView
from django.urls import reverse_lazy
from django.conf import settings

import os

from PIL import Image

from moviepy.video.io.VideoFileClip import VideoFileClip

from .models import Course, Videos

class VideoListView(ListView):
    model = Videos
    context_object_name = 'videos'
    ordering = ['-date_created']
    paginate_by = 5

class UserVideoListView(ListView):
    model = Videos
    template_name = 'courses/user_videos.html' # <app>/<model>_<viewtype>.html
    context_object_name = 'videos'
    paginate_by = 5

    def get_queryset(self):
        user = get_object_or_404(User, username=self.kwargs.get('username'))
        return Videos.objects.filter(author=user).order_by('-date_created')

class VideoDetailView(DetailView):
    model = Videos

class VideoCreateView(LoginRequiredMixin, CreateView):
    model = Videos
    fields = ['title', 'video', 'course']

    def form_valid(self, form):
        form.instance.author = self.request.user

        # Get the current video object being saved
        video_obj = form.save()

        # Load the video clip
        clip = VideoFileClip(os.path.join(settings.MEDIA_ROOT, str(video_obj.video)))

        # Get the first frame of the video as an image
        thumbnail = clip.get_frame(0)

        # Convert the array to a PIL image
        thumbnail_image = Image.fromarray(thumbnail)

        # Save the thumbnail as an image file
        thumbnail_filename = os.path.join(settings.MEDIA_ROOT, "thumbnails", f'{str(video_obj.id)}_thumb.jpeg')
        thumbnail_image.save(thumbnail_filename, format='JPEG')

        print(f"FILENAME IS: {thumbnail_filename}")

        # Set the thumbnail_url field of the video object to the path of the thumbnail file
        video_obj.thumbnail = os.path.join('thumbnails', f'{str(video_obj.id)}_thumb.jpeg')
        video_obj.save()

        return super().form_valid(form)

class VideoUpdateView(LoginRequiredMixin, UserPassesTestMixin, UpdateView):
    model = Videos
    fields = ['title', 'video']

    def form_valid(self, form):
        form.instance.author = self.request.user
        return super().form_valid(form)

    def test_func(self):
        video = self.get_object()

        if self.request.user == video.author:
            return True

        return False

class VideoDeleteView(LoginRequiredMixin, UserPassesTestMixin, DeleteView):
    model = Videos
    success_url = '/'

    def test_func(self):
        video = self.get_object()

        if self.request.user == video.author:
            return True

        return False

class CourseListView(ListView):
    model = Course
    context_object_name = 'courses'

class CourseDetailView(DetailView):
    model = Course
    context_object_name = 'course'

class CourseCreateView(CreateView):
    model = Course
    fields = ['title', 'description', 'author', 'tags']
    success_url = reverse_lazy('course-list')

class CourseUpdateView(LoginRequiredMixin, UpdateView):
    model = Course
    fields = ['title', 'description', 'author', 'tags']
    context_object_name = 'course'

class CourseDeleteView(LoginRequiredMixin, DeleteView):
    model = Course
    success_url = reverse_lazy('course-list')
    context_object_name = 'course'

I am not sure if this is the correct. I am looking for some suggestions and feedback.

Just a couple notes - things that jump out at me in a brief review:

  • In Videos you have:

Since Chapter already has an established relationship with Course, having course as a separate ForeignKey to Course is redundant and a potential problem.

  • The common convention for Model names is that they are singular - which you have for Course and Chapter. However, you have Videos as a model name for what may more appropriately be Video.

  • In your VideoCreateView, you’re accessing the file by finding the file from the upload directory. That’s unnecessary. The object video_obj.video gives you access to a FieldFile object that you can access directly.

  • Likewise, when you create the image file, you should do so using those same FileField and FieldFile APIs.

When you say,

Since Chapter already has an established relationship with Course, having course as a separate ForeignKey to Course is redundant and a potential problem.

Did you meant

“having course as a separate ForeignKey to Video is redundant and a potential problem.”

The common convention for Model names is that they are singular - which you have for Course and Chapter. However, you have Videos as a model name for what may more appropriately be Video.

OK, I changed the model to Video instead of Videos.

  • In your VideoCreateView, you’re accessing the file by finding the file from the upload directory. That’s unnecessary. The object video_obj.video gives you access to a FieldFile object that you can access directly.
  • Likewise, when you create the image file, you should do so using those same FileField and FieldFile APIs.

Something like this you mean?

        clip = VideoFileClip(video_obj.video.path)

I changed the Create view a bit, added file hash for the thumbnail name instead of video id.

class VideoCreateView(LoginRequiredMixin, CreateView):
    model = Video
    fields = ['title', 'video', 'course']

    def form_valid(self, form):
        form.instance.author = self.request.user

        # Get the current video object being saved
        video_obj = form.save()

        # Load the video clip
        clip = VideoFileClip(video_obj.video.path)

        # Get the first frame of the video as an image
        thumbnail = clip.get_frame(0)

        # Convert the array to a PIL image
        thumbnail_image = Image.fromarray(thumbnail)

        with open(video_obj.video.path, 'rb') as f:
            file_hash = hashlib.sha256(f.read()).hexdigest()

        # Save the thumbnail as an image file
        thumbnail_filename = os.path.join(settings.MEDIA_ROOT, "thumbnails", f'{file_hash}_thumb.jpeg')
        thumbnail_image.save(thumbnail_filename, format='JPEG')

        print(f"FILENAME: {os.path.basename(form.instance.video.url)}")

        # Set the thumbnail_url field of the video object to the path of the thumbnail file
        video_obj.thumbnail = os.path.join('thumbnails', f'{file_hash}_thumb.jpeg')
        video_obj.save()

        return super().form_valid(form)

In production I would probably have some env var where the user would store their course videos. And do something like,

        thumbnail_filename = os.path.join(USER_MEDIA_DIR, "thumbnails", f'{file_hash}_thumb.jpeg')

One of my concern ATM is that with all these models and views, the user has to create a course and upload videos separately and assign those videos to a course manually and there is no way for manipulate chapters ATM, but all this feels wrong.

It should be that the user just fills a single form where they enter,

  • Course name
  • Course author
  • Course description
  • Tags (for filtering)
  • Add a directory which points to the course (which might have sub-dirs as chapters or just video file(s))

and nothing else.

Should I re-consider the current design of the models and views I have? What do you think?

No, I meant that having the ForeignKey field named course in your Video model, where course is a foreign key to Course, is redundant and a potential problem.

You already have a foreign key to Chapter in Video, and Chapter has a foreign key to Course. That means you can identify a Course for a Video by following the chain of foreign keys from Video to Chapter to Course. Having the direct link between Video to Course in addition to that is the potential problem.

Actually, that depends upon what VideoFileClip is expecting to be passed. It may also work with video_obj.video. By accessing that field, Django gives you a FieldFile object, which you can open and use. See the docs and examples at Managing files | Django documentation | Django

Personally, other than the issue with the course variable in Video, I think your models are fundamentally fine. (But I don’t know your complete set of objectives and requirements to say that with any degree of certainty.)

Do not link the two (models and views) together in your mind as inseparable concepts. Your models should be designed around what’s going to facilitate your data storage and retrieval requirements. Your views (or more accurately, the pages generated by the views) should be designed around what you want the users to see.

It’s the job of the views and models working together to tie those two elements together.

For example, you could build a single “Create Course” page that uses an inline formset to create the chapters, where each chapter contains an inline formset to manage the videos.

Yes, it’s significantly more work than what you have, but it can be done. It’s only a question of how much work you want to put into this.

No, I meant that having the ForeignKey field named course in your Video model, where course is a foreign key to Course, is redundant and a potential problem.

You already have a foreign key to Chapter in Video, and Chapter has a foreign key to Course.

So I should rename the field course and chapter in Chapter model and remove the course field from the Video model?

That means you can identify a Course for a Video by following the chain of foreign keys from Video to Chapter to Course. Having the direct link between Video to Course in addition to that is the potential problem.

I see. So I can do Course.objects.all().video.thumbnail and so on…?

Do not link the two (models and views) together in your mind as inseparable concepts. Your models should be designed around what’s going to facilitate your data storage and retrieval requirements. Your views (or more accurately, the pages generated by the views) should be designed around what you want the users to see.

Hmm… Interesting. I didn’t think it this way. I had the idea that I need to have a view for all the models I create.

It’s the job of the views and models working together to tie those two elements together.

For example, you could build a single “Create Course” page that uses an inline formset to create the chapters, where each chapter contains an inline formset to manage the videos.

I see. But then I need to create a custom form maybe in forms.py instead of using the generic CreateView class as I need to include fields from other models as well and possibly add additional fields as needed.

Yes, it’s significantly more work than what you have, but it can be done. It’s only a question of how much work you want to put into this.

Not a big deal, I am enjoying django so far. I can manage :smiley:

You shouldn’t change anything in Chapter. Just remove course from Video.

Not precisely like that, but essentially correct. Review the docs at Related objects reference | Django documentation | Django

Or a couple of different forms.

I definitely would not use the Django-provided generic CBVs for this. This is one of those cases where you’re either going to want to create your own CBVs if you really want to go that route, or use FBVs.

And, if you’re talking about using formsets, you may even be looking at some views designed to work with AJAX calls to dynamically add forms to the formset.

You shouldn’t change anything in Chapter. Just remove course from Video.

My idea for having course in both Chapter and Video was, what if there are no sub-dirs but only a single file, then the course would have no chapters but only a single file like those FreeCodeCamp videos, they are usually 1 big video file 3-5 and up to 10 or more hours even.

I definitely would not use the Django-provided generic CBVs for this. This is one of those cases where you’re either going to want to create your own CBVs if you really want to go that route, or use FBVs.

And, if you’re talking about using formsets, you may even be looking at some views designed to work with AJAX calls to dynamically add forms to the formset.

I ended up deciding to have only 1 field in the form, because the rest should be filed automatically according to the course or what user wants (later in the update view).

This is my models.py (pretty much same, except I added null and blank to some fields),

from django.db import models
from django.contrib.auth.models import User
from django.core.validators import RegexValidator, FileExtensionValidator
from django.urls import reverse

from taggit.managers import TaggableManager

# Create your models here.
class Course(models.Model):
    title = models.CharField(max_length=150)
    author = models.CharField(max_length=150, null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    created_by = models.ForeignKey(User, on_delete=models.CASCADE)
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)
    tags = TaggableManager()

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('course-detail', kwargs={'pk': self.pk})

class Chapter(models.Model):
    course = models.ForeignKey(Course, on_delete=models.CASCADE, related_name='chapters')
    title = models.CharField(max_length=255)
    order = models.IntegerField(default=0)
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('video-detail', kwargs={'pk': self.pk})

class Video(models.Model):
    course = models.ForeignKey(Course, on_delete=models.CASCADE, related_name='videos')
    chapter = models.ForeignKey(Chapter, on_delete=models.CASCADE, related_name='videos', null=True, blank=True)
    title = models.CharField(max_length=150)
    video = models.FileField(upload_to='videos',null=True, validators=[
        FileExtensionValidator(allowed_extensions=[
            'MOV',
            'avi',
            'mp4',
            'webm',
            'mkv'
        ])
    ])
    thumbnail = models.ImageField(upload_to='thumbnails')
    description = models.TextField(null=True, blank=True)
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('video-detail', kwargs={'pk': self.pk})

    def delete(self, *args, **kwargs):
        # Check if this is the last video in the course
        if self.course.videos.count() == 1:
            # Delete the course and any associated chapters
            self.course.delete()

        super().delete(*args, **kwargs)

This is forms.py,

from django import forms

from .models import Course, Chapter, Video

class CreateNewCourseForm(forms.ModelForm):
    directory = forms.CharField(max_length=300)

    class Meta:
        model = Course

and this is views.py,

@login_required
def create_new_course(request):
    if request.method == 'POST':
        form = CreateNewCourseForm(request.POST)

        if form.is_valid():
            directory = form.cleaned_data['directory']

            # Loop through each subdirectory to create a course
            courses_created = 0

            for path in Path(directory).iterdir():
                if path.is_file():
                    ext = path.suffix.lower()

                    if ext in ['.mov', '.avi', '.mp4', '.webm', '.mkv']:
                        course = Course(title=path.stem, created_by=request.user)
                        course.save()

                        courses_created += 1

                        # Create the video object and set the video field to the new file path
                        video_path = os.path.join(settings.MEDIA_ROOT, 'videos', path.name)
                        shutil.copy(path, video_path)

                        video = Video(title=path.stem,
                                      video=os.path.realpath(video_path),
                                      course=course)
                        video.save()

                        # Load the video clip
                        clip = VideoFileClip(video_path)

                        # Get the first frame of the video as an image
                        thumbnail = clip.get_frame(0)

                        # Convert the array to a PIL image
                        thumbnail_image = Image.fromarray(thumbnail)

                        with open(video_path, 'rb') as f:
                            file_hash = hashlib.sha256(f.read()).hexdigest()

                        # Save the thumbnail as an image file
                        thumbnail_filename = os.path.join(settings.MEDIA_ROOT,
                                                          "thumbnails",
                                                          f'{file_hash}_thumb.jpeg')
                        thumbnail_image.save(thumbnail_filename, format='JPEG')

                        # Set the thumbnail_url field of the video object to the path of the thumbnail file
                        video.thumbnail = os.path.join('thumbnails', f'{file_hash}_thumb.jpeg')
                        video.save()

                elif path.is_dir():
                    course = Course(title=path.name, created_by=request.user)
                    course.save()

                    courses_created += 1

                    # Loop through subdirectories to create chapters
                    for subpath in path.iterdir():
                        if subpath.is_dir():
                            chapter = Chapter(title=subpath.name, course=course)
                            chapter.save()

                            # Loop through video files to create videos
                            for file_path in subpath.glob('*'):
                                if file_path.is_file():
                                    ext = file_path.suffix.lower()

                                    if ext in ['.mov', '.avi', '.mp4', '.webm', '.mkv']:
                                        # Save the video file to the MEDIA_ROOT directory
                                        video_path = os.path.join(settings.MEDIA_ROOT, 'videos', file_path.name)
                                        shutil.copy(file_path, video_path)

                                        video = Video(title=file_path.stem,
                                                      video=os.path.join(subpath, file_path.name),
                                                      chapter=chapter,
                                                      course=course)
                                        video.save()

                                        # Load the video clip
                                        clip = VideoFileClip(video_path)

                                        # Get the first frame of the video as an image
                                        thumbnail = clip.get_frame(0)

                                        # Convert the array to a PIL image
                                        thumbnail_image = Image.fromarray(thumbnail)

                                        with open(video_path, 'rb') as f:
                                            file_hash = hashlib.sha256(f.read()).hexdigest()

                                        # Save the thumbnail as an image file
                                        thumbnail_filename = os.path.join(settings.MEDIA_ROOT,
                                                                          "thumbnails",
                                                                          f'{file_hash}_thumb.jpeg')
                                        thumbnail_image.save(thumbnail_filename, format='JPEG')

                                        # Set the thumbnail_url field of the video object to the path of the thumbnail file
                                        video.thumbnail = os.path.join('thumbnails', f'{file_hash}_thumb.jpeg')
                                        video.save()

            if courses_created == 0:
                return HttpResponseBadRequest('No courses found in directory')

            return redirect('home')
    else:
        form = CreateNewCourseForm()

    return render(request, 'courses/form.html', {'form': form})

It appears to be working as expected. Though I should optimize the view function as its doing so much every iteration.

Now the user just enter a path to directory where they store their course and this view loops recursively over the directory making the initial directory as a Course further sub-dirs as Chapters (if exists) and any files under the sub-dirs as Video, also files immediate in the parent directory are considered separate Course as well.

Is there a DirField or something that can take a directory as input instead of a file with FileField. That would make this form a little nicer, as user can browse instead of copying path and pasting in the CharField.

The only problem is now is that I need to figure out how to monitor this directory for changes for example user adds another directory or file for new Course, then it should automatically add that to the DB as well.

What do you think? Is this overreaching or is it fine but needs some improvements and optimizations?

That’s fine, but that’s a “special case” that can/should be handled in code and not by creating models that creates potential problems in other cases. I would treat this as a Course with 1 chapter, not as a Course with 0 chapters. How you represent that visually is a completely separate issue.

Regarding the directories, you may wish to logically separate the directory/file organization in your file storage from the internal representation maintained for the benefit of the users. There is no reason to require that a file being shown to the user as “Introduction to Django / Chapter 1 / Installing Python” needs to be maintained in the file system as “Introduction to Django/Chapter 1/Installing Python”.

Architecturally, this is a “Separation of Concerns” topic. You want your physical storage to facilitate usage. You have other factors that you may wish to consider. For example, what happens if you want to move these files to a Cloud-based storage, such as AWS S3? How much code are you going to need to change to support that? What happens if you want to allow for non-standard characters in Course, Chapter, or Video names? Is the underlying storage going to work with that?

Use the file storage to store the media. Use the database to store the metadata regarding that media, don’t rely upon the file storage environment to do that for you.