Thinking in sub tables

[eddie5]

When designing a database with Wappler’s new sub table feature in mind, it seems that the user table ultimately ends up being the master table in most situations. Some examples…

• Comments could not exist without blog posts and blog posts cold not exist without users (authors).

• Order items could not exist without orders and orders could not exist without users (customers).

• Course pages could not exist without courses and courses could not exist without users (teachers or students).

So does everything always pretty much come back to users being the master table or am I thinking about sub tables in the wrong way?

Much appreciated.

[nshkrsh]

a comment will be made by a user.
an order will be placed by a user.
hence for these cases, your inference is correct.

but courses will exist independent of the users. users can come and subscribe to the courses - but those courses will be listed before users start coming in. (really depends on the app type, if the courses are only created by teachers, then yes, there is a dependency, but if there courses offered by the platform, then it is independent of users or perhaps there is a system user that owns those courses).

hope this helps.

[eddie5]

Yes, that does help. Thank you @nshkrsh!

In my course scenario both students and teachers are users and a course cannot exist without a teacher first creating that course.

[George]

Well you should think more in primary items, then start with them.

Like courses exists indeed and have to have a teacher - but also students and the teacher can change. So it is the course is primary here.

The course must have a sub table - students (course_students), of those students that attend the course, but this sub table refers to general global students table for the student info. This is done when you have many to many relations. A course can have many students and a student can follow multiple courses.

[eddie5]

Thanks for that breakdown, @George…very helpful.

Your example of the course_students table serving as a bridge table in a many to many relationship helped further clarify it for me.

[Hyperbytes]

Sorry, late to the show (again) but think this is important

Sub tables and joins work differently and therefore their output is not the same so in some cases joins are appropriate, others sub tables

So if i take a simple users table (this is from the sample data in docker) and I add to it a sub table of role

Now to illustrate the difference i now add a separate role table

Both role tables have the same content, two roles “A” and “E” attached to user id = 1

I then create two queries, one using a join, the other via sub tables

the query named join uses an inner

The query called sub table uses a sub table

So identical data will give the same result? NO

Running the query and examining the output we see

Note the join returns two full rows with the data effectively merged.

The sub table query returned nested data, one record for the id and a nested list for the roles

I suspect this results from Wappler using nested queries to manage sub tables and this does not return the same as a join

So when deciding to use nested tables or joins you must consider how you will use the data and in which form best suits your specific needs

Hope this helps