From a performance speed perspective I saw that joins are faster than sub tables in most cases when researching online, is this the same for in Wappler? just looking into how my database would be best structured
Sub tables are just like any other tables with relation to a parent table.
You probably want to compare joins agains nested queries with sub tables.
The difference between those two is the amount of data and the structure. There isn’t much of performance difference.
A regular query with a join to a sub table will actually produce a lot of data replicating the parent records to all sub table records and you will need to filter or group this data later on. It is done in a single query.
While a nested query on sub table is actually nesting the sub table results directly under each parent record. So everything is already grouped as you would expect. Nested queries does add an additional query per level but because everything is filtered and relational let’s are used, there is no much of a performance hit.
So it is actually much more about convenience and logically grouping data with nested queries with sub tables than performance - both ways perform similar.
And lastly you can always query a sub table directly just as any other table.
1 Like
Just a sort of off topic question about sub tables. Sorry.
If I created my tables in Navicat but they are related/linked with a foreign key, should they be showing up as sub tables in the DB Manager?
Well sub tables are indeed regular tables linked with foreign keys to their parent table. They only also have the parent table as prefix to their own name.
So if your sub tables are also following this naming convention then yes they will automatically show in the database manager.
The database manager makes it just much more easy to create sub tables as it adds the right prefix for you and also auto creates all needed foreign keys.
The foreign keys are hidden in the basic view of the database manager, as those are considered implementation details, but you can see them if you want by switching to advanced view.
1 Like