Creating custom tables

[jonathanbossenger]

Details

• Content type (Online Workshop, Lesson, Course, Tutorial, or Lesson Plan): Lesson
• Content title: Creating custom tables
• Topic description: Creating, versioning, updating schema, and deleting a custom database table.
• Audience (User, Developer, Designer, Contributor, etc.): Developer
• Experience Level (Beginner, Intermediate, Advanced, Any): Intermediate

Prerequisites

It is assumed that the learner has already completed the following lessons:

• Beginner WordPress developer
• Modules 1 - 6

Learning Objectives

• Explain why a plugin developer might need a custom database table
• Describe the process of creating a custom database table

Related Resources and Other Notes

• https://developer.wordpress.org/plugins/creating-tables-with-plugins/
• https://learn.wordpress.org/tutorial/custom-database-tables/

Automation Code

//lesson

[Piyopiyo-Kitsune]

@jonathanbossenger will this be included in a developer pathway?

[jonathanbossenger]

Yup, updated.

[MichelleBlanchette]

There's many concepts to cover for this, as your linked comment mentions @jonathanbossenger .

With just two lessons, maybe this lesson should cover the concept of "managing" database tables (creating, versioning/updating, deleting - life cycle) while the second lesson is about querying against custom database tables..? Or should this module be broken out into a few more specific lessons?

"Creating" and then "interacting" with custom database tables seems like an odd segmentation to me, but I could be missing the concept/organization you have in mind.

[jonathanbossenger]

First off, relevant developer doc for reference: https://developer.wordpress.org/plugins/creating-tables-with-plugins/

Second, you're right, "Creating" is probably the wrong word to use here, "Managing" would be better.

The way I envisioned this was:

• Lesson one, this lesson: creating, versioning, updating schema, and deleting of a custom table, as per the developer doc but without the section on Adding initial data
• Lesson two, the interacting lesson, everything to do with querying against custom a custom table, including proper usage of wpdb, sanitizing queries for inserts and updates, etc

I should also point out that it should introduce concepts and then refer to documentation. So, for example, we don't need to go into every single method available on wpdb, but we can show maybe two (insert and query) and then link to the docs for further reading.

[jonathanbossenger]

The content for lesson one has already been created in this tutorial: https://learn.wordpress.org/tutorial/custom-database-tables/, so we might just be able to reuse it as is.

[jonathanbossenger]

See also this feedback on the original tutorial, which includes the general structure of the next lesson: #1867 (comment)

[github-actions]

Lesson Development Checklist

• Gather any relevant links to Support, Docs, or related material
• Description and Objectives finalized
• Lesson created and announced to the team for review
• Lesson reviewed
• Lesson video submitted and published to WPTV
• Lesson created on Learn.WordPress.org
• Lesson video published to YouTube
• Lesson on Learn.WordPress.org updated with YouTube video
• Lesson published to Learn.WordPress.org

[jonathanbossenger]

@MichelleBlanchette I've updated the description of this issue to use the new format from the new Lesson creation process. It now also includes the description we discussed, the learning outcomes and the relevant links.

[jonathanbossenger]

I've assigned the ticket to you as discussed.

What would also be helpful is if you're able to give me an ETA for creating the video lesson.

Please make sure to give yourself enough time outside of your work responsibilities and personal life.

[jonathanbossenger]

Feel free to ping me here with any further questions.

[MichelleBlanchette]

Thanks @jonathanbossenger ! Can we also update the lesson title to better reflect the topics covered? We mentioned "Managing" before in place of just "Creating".

[MichelleBlanchette]

@jonathanbossenger

Script is ready for review here: https://github.com/wptrainingteam/learn-wp-content/blob/45d813c798886959fc11bd304c4c1f6372767d77/plugin-developer-learning-pathway/07-Custom-Database-Tables/01-Creating-and-Managing-Custom-Tables.md

Per pull request here: wptrainingteam/learn-wp-content#11

Code snippets have been tested and are included in an accompanying plugin file with a .wp-env.json environment for ease.

All feedback is humbly welcomed. 😃

[jonathanbossenger]

@MichelleBlanchette thank you for your work on the script. I plan to review it this week.

In the meantime, do you feel comfortable to start on the script for #2721 or would you prefer to wrap up one lesson (script and video) at a time?

[MichelleBlanchette]

I'm happy to work on both in parallel. Thank you @jonathanbossenger !

[MichelleBlanchette]

While working on the script for the next lesson #2721, I'm realizing I should probably explain wpdb at the beginning of this lesson instead. It's referenced quite a bit already in this one without any introduction, and it's feeling awkwardly late to try introducing it in the next lesson about queries.

I'll set PR wptrainingteam/learn-wp-content#11 to Draft for now so I can make this change.

[MichelleBlanchette]

Pull request is ready for review again: wptrainingteam/learn-wp-content#11

Script can be reviewed here: https://github.com/wptrainingteam/learn-wp-content/blob/70e0ea229e7df3363cd9b03691558a4374c3a13b/plugin-developer-learning-pathway/07-Custom-Database-Tables/01-Creating-and-Managing-Custom-Tables.md

I'm thinking I could include more links to documentation throughout the article as I started doing in Lesson 02 (#2721), but I'd like to also start getting others' feedback on this sooner than later since I can only work on this for limited chunks of time.

@jonathanbossenger Can we mark this as "script ready for review", if there is such a status? Thank you!

[jonathanbossenger]

Thanks @MichelleBlanchette

@jonathanbossenger Can we mark this as "script ready for review", if there is such a status? Thank you!

We don't have that status currently. In the past the process from idea to video has been managed by one contributor, possibly with an SME (subject matter expert) working alongside them in an editorial role reviewing any work in progress. We only open for more general review once the first video draft is ready.

Are you ok with me operating as the SME/editor with you for these lessons?

It also raises a deeper question about possibly amending the review process, which I will raise with the training team in the next meeting on Thursday.

[MichelleBlanchette]

Oh, okay. I was thinking the script should be confirmed before I record it and edit it all together so I don't do too much wasted effort. But I'm fine with whatever you recommend, @jonathanbossenger !

[jonathanbossenger]

I do understand that concern. Let's see if there's anything I suggest changing, and then I'll ping one or two developer folks in the training team to get them to review the script. We could also ask anyone participating in the content creation in the main tracking issue to review as well.

[jonathanbossenger]

I plan to review the script tomorrow.

[jonathanbossenger]

@MichelleBlanchette I've created a PR with my review suggestions: wptrainingteam/learn-wp-content#14

I had wanted to use the GitHub PR review UI to leave comments and suggestions, but I was working offline. In future I will try and review that way instead, as it allows for better collaboration.

[MichelleBlanchette]

@jonathanbossenger I'm happy to record the audio and edit the video next weekend if we can get the final round of edits approved and merged in wptrainingteam/learn-wp-content#14 during this week.

Thank you for the great collaboration and feedback. 😀

[jonathanbossenger]

I've closed all open discussions bar one, which I think is more just a conversation around what to include in the next lesson.

[MichelleBlanchette]

@jonathanbossenger I worked on the audio + video for this lesson all day today and last night. I didn't realize how much effort it takes to produce these videos, so I appreciate your patience! 😅

Looks like this lesson will be 11 minutes long, so it's quite a lot and sometimes I struggle coming up with visuals for some of the script. Also, I don't know if my process is optimal since I don't have much experience in making videos.

I recorded the audio last night, and now I'm recording snippets of a Google Slides presentation and on-screen coding while listening to the audio track for timing. It's a bit tricky and I'm making it all as I go along, but I'm almost done with the first draft!

[jonathanbossenger]

@MichelleBlanchette, thanks for the update.

Yes, video creation, especially if it is your first time, can be a lengthy process. I recommend taking your time and allowing yourself to find the workflow that works for you. I don't think there is a specific "optimal" process to follow, it differs from content creator to content creator.

If it helps, the training team also has some resources for creating video content

• https://make.wordpress.org/training/handbook/lessons/creating-a-lesson/
• https://make.wordpress.org/training/handbook/guidelines/video-best-practices/

The Creating a Lesson page also includes links to the process Wes and I follow. This will give you some idea of our personal process and how it differs.

[MichelleBlanchette]

Quick update for tonight: I finished editing the video. I'll just do a bit more final review on my end before exporting it for peer review. I'll be busy tomorrow, so I'm expecting it'll be ready for peer review hopefully by the end of this week. 😃

[MichelleBlanchette]

I reviewed the video this evening and think it's ready enough to start hearing other's thoughts rather than me endlessly scrutinizing details on my own. 😅

Pull Request for audio + video is here: wptrainingteam/learn-wp-content#17

[jonathanbossenger]

Thanks @MichelleBlanchette

I'm adding the url to the video here, just for tracking purposes: https://drive.google.com/file/d/11ksYVmaf1DhEwG_4ks7wKt9GNSQ4CP6N/view?usp=sharing

I'll do my best to review this next week, and let you know if I have any specific feedback, before we open it to the rest of the training team for review.

[jonathanbossenger]

Hi @MichelleBlanchette. Apologies that this feedback is so late, I've been working on other things that kept me distracted.

Excellent work on the video.

• I appreciate how you showed your code examples and highlighted the relevant sections.
• I also liked how you incorporated graphics into the presentation, like the MySQL table schema and the representation of the wpdb class early on

I have two pieces of specific feedback I'd like to discuss with you.

The first one might be harder to implement in this video, but let's chat about it.

The training team's Video Best Practices guidelines for creating technical videos that include code examples suggest using a light mode theme wherever possible. This was based on feedback I got very early on from reviewers that a light-mode theme makes it easier for folks to read code in a video lesson and, therefore, more accessible than a dark-mode theme.

I realise that this request will mean quite an update to this video. So, I'd like to know if you're opposed to trying a light-mode theme for the next video and any subsequent videos you create.

The second piece of feedback might be easier to implement. Early on, a lot of feedback I got from folks learning development from the videos was that it is useful to use an external form of annotation to highlight any specific code on the screen.

I also used to do something similar to what you do, using the cursor onscreen to point out the code I'm describing, but the general feedback was that some form of arrow or other overlay makes it clearer.

So what I've started doing is using arrows to highlight a single line of code (example here) and a bordered box to highlight sections of code (example here from around 2:46)

Let me know what you think of these ideas.

[MichelleBlanchette]

@jonathanbossenger I have no issue with implementing these two items for this video. In fact, it sounds like it might even be easier than what I did because then the code is more static and I just flip through screenshots with the arrows or outlines like I do in earlier segments of the video already.

I want to get it right and be consistent with what is expected on WordPress Learn, so I really appreciate your feedback!

I was actually wondering if my database screenshots are alright? It's dark mode of TablePlus's free version. I think WPorg always shows and mentions PHPMyAdmin in light mode. Should I use screenshots of that instead as well?

It's the holiday season for me, so I might have more time to work on this over my holiday next week—as well as Lesson 2 of this module, which I still need to get back to.

[MichelleBlanchette]

@jonathanbossenger I've updated the video with more arrows/outlines and with light-themed VS Code screens:
https://drive.google.com/file/d/1um-fqTkDy71K1lpVtG_oNrc7ug1I6UMn/view?usp=sharing

I also added a slide to better explain the $wpdb->prefix values between single sites and multisite subsites, which really helps that section flow better.

Thank you for the initial review and feedback! I'm much happier with this version of the video!

Please let me know if you have any other thoughts or if we can open this up for wider review. 😀

[jonathanbossenger]

Thanks @MichelleBlanchette it looks great. I'm going to open this up for wider review.

One small note, for future videos, perhaps also consider switching your database tool to a light mode theme as well. A general rule I follow is to make everything default to light mode for video recordings.

[jonathanbossenger]

This video lesson is ready for review, please follow the Guidelines for Reviewing Content.