What is the ideal table structure, where courses have slide-based lessons with unique content types?

gaurang_workshore

Hello everyone 👋,

I'm new to Xano and working on a project which is about learning management system. I'm using Webflow + Wized + Xano.

So here I have 2 tables "Courses" and "Lessons".
Courses table will have list of course name and some more information about that course.
Lessons table will have content for each course.

For lessons we are showing content as a slide.
Here's the structure I've created right now.

Table: Courses
Columns:

• course_id
• course_name
• course_image
• course_description

Table: Lessons
Columns:

• lesson_id
• course_id (referencing course_id in Courses table)
• slide_number
• slide_image
• slide_text

For each slide I have unique set of content. For
Slide 1 it will be just 1 image field and 1 text field.
Slide 2 will have 1 image and 2 paragraph.
Slide 3 will have different type of fields and so on…

Since each slide will have unique set of content type I've no idea on setting up database. Each database entry act as a slide so should I create tables for each slides? like slide 1, slide 2? Also for each courses slide content setup will be same just content will change and some course might have 5 slide and some have more than that.

Can some one help me how to create table structure with this requirements?
I saw amazing tutorial on Xano+Wized by @Chris Coleman which was really helpful for me. If you can help me?

I've created static slider for demo purpose for better understanding. You can check here: https://drive.google.com/file/d/1z7s9IXMqVMfAobAd6XLwmqdeyemTFOTE/view?usp=sharing

jakespirek

Hi Gaurang 👋

Thanks for sharing your question here!

I think this could totally be doable with the tables you mentioned, depending on the max number of images/text variations that you might need on a slide.

If there's going to be a wider variety you might consider a third table for the lesson content blocks. In this table you'd have a text field, image field, video field, etc to handle any possible variations. If a block doesn't have video or an image, those fields are just left blank.

So maybe Lesson 1 just has 1 content block of text + image.

But maybe Lesson 2 has 3 blocks: text, image, video.

When you go to display the content for each Lesson, you could treat each of those blocks the way you'd treat Webflow CMS items loading in a list.

Lesson 1 would only have 1 item (block) to display, while Lesson 2 would have 3 items in its list, representing those 3 blocks.

I'm sure there are plenty of other ways to solve this, and others can chime in with their ideas, but hopefully this at least sparks some initial ideas!

Happy building! 💪

Guillaume Maison

Hi @gaurang_workshore

I would build it this way (giving SQL way) :

CREATE TYPE "slideTemplate" AS ENUM (
'template1',
'img1text1',
'img1text2'
);

CREATE TYPE "assetType" AS ENUM (
'text',
'Image',
'link'
);

CREATE TABLE "courses" (
"course_id" int UNIQUE PRIMARY KEY,
"course_name" text,
"course_image" image,
"course_description" text
);

CREATE TABLE "slides" (
"slide_id" int UNIQUE PRIMARY KEY,

"slide_course_id" int,
"slide_slideType" slideTemplate,
"slide_numOrder" int
);

CREATE TABLE "slideAsset" (
"slideAsset_id" int UNIQUE PRIMARY KEY,
"slideAsset_slide_id" int,
"slideAsset_type" assetType,
"slideAsset_numOrder" int,
"slideAsset_textValue" text,
"slideAsset_imageValue" text,
"slideAsset_linkValue" text
);

COMMENT ON TABLE "slides" IS 'a Course is made of several slides.
Each slide is based on a template
For each slide, in the front end, create as many slideAssets as needed by template def';

COMMENT ON TABLE "slideAsset" IS 'Each slide can have one or more assets.
Each asset can be of a type, whether the enum or configured as a string value';

ALTER TABLE "courses" ADD FOREIGN KEY ("course_id") REFERENCES "slides" ("slide_course_id");

ALTER TABLE "slides" ADD FOREIGN KEY ("slide_id") REFERENCES "slideAsset" ("slideAsset_slide_id");

The only thing is that on your frontend, you'll have to determine, when you design a slide, the number of assets to be created.
After that, when displaying a slide (i renamed ;) ), you can retrieve all the needed assets, which are typed …

Let me know if you have any question :)