Resource – Live Events- Database Design With Caspio

Video Synopsis

Discover how to structure, optimize and future-proof your online databases.

• Introduction
• E-commerce Example
• Many-To-Many Table
• Joining Table
• Linking Tables
• Highschool Example
• Random ID VS Auto Number VS GUID
• Organizing Tables
• Linking the High School Example Tables
• How Do You Know Which Tables to Make?
• Additional Relationship Settings
• Referential Cascade Delete
• Project Management Example
• Linking The Project Management Example Tables
• Next Week’s Livestream Topic
• When Would You Have Two Tables Versus One for Contacts and Clients?
• Caspio Demo Calendar
• Conclusion

Speaker

• Ned Pajic, Technical Evangalist, Caspio

Transcript

Ned Pajic: Okay, let me try that again. Can you hear me now? Okay. Hopefully this works. Let me know. Yeah. Okay, good. Okay. All right. Welcome, everyone. I didn’t make any changes to my setting, so I’m a little surprised on my end why you guys weren’t able to hear me from the beginning. I’ll take a look at my OBS software later on.

Introduction

Hey, welcome back, everyone. Thank you so much for coming back to these live streams. The live streams largely depend on your attendance. So as long as you guys keep coming back to these streams, the live stream will continue, okay? So I appreciate you guys coming back and learning how to use the platform. So thank you for that. And even today, we have some exciting content. We’re gonna talk about database design, and I want you guys to see the process that I go through, personally, before I build my applications in Caspio, how I like to put together my tables, because ultimately, what it’s gonna boil down to is how we put our tables together to have a successful application.

Okay. And what that basically means is we have to have the ability to translate the application’s functionality ultimately into tables. And it’s going to translate into one-to-many and many-to-many relationships. So we have to have the ability to break down the tables depending on the functionality of the apps. So if we’re looking at, let’s say, customers and orders, that’s gonna be a one-to-many relationship because one customer can have many orders. If we’re looking at projects and tasks, one project can be linked to many tasks. So we gotta be able to know how to separate our tables and how to link our tables using the primary keys and foreign keys. And today we’re gonna look at three different use cases.

Okay. We’re gonna be looking at an e-commerce example, something similar to Amazon. Then I decided to bring up an education example where we have, like, a high school setting. We can have classrooms, we can have parents, teachers, grades exams, and that one is going to have 11 tables. And the last example that we’re gonna look at today is a project management application, which also has 11 tables. And that will have clients, we’ll have contacts, we’ll have projects, tasks, milestones, hours, and a couple of lookup tables as well. If you guys would like to see additional database designs in the future, let me know in the chat window, and maybe we can bring this live stream back and we can look at something a little bit more complex or something a little bit easier, something a little bit more simpler. All right.

So I know I took the note from last live stream. Someone told me to show the finished product first, before I show you how to build it, but I don’t think it makes a lot of sense for database design. Just because if you look at all of the finished tables and all the lines, connecting primary keys and foreign keys, it might look a bit convoluted and confusing, especially to those who don’t have a database background. So today I think it makes a little bit more sense to work on it together from start to finish so that you can see how I go about building my tables and how I connect my tables before I build anything else in Caspio, any form, any report, any view or any authentication. So again, it’s very important and critical to have the tables built correctly before we move on to the other objects. Okay?

E-commerce Example

So let’s take a look at our first example. Let me make sure you can see my screen and you should be able to. So let’s start by building a new app and we’re not gonna be importing any data today. Let’s build the app from scratch and let’s begin with the e-commerce app.

So let’s open it. And again, always, the very starting point of any application in Caspio is to go directly to tables. And I always like to build my tables first. Again, before I do anything else with the other objects, I’ll build my tables. Now, I also don’t like to put all of the fields in my table right away. The only fields that I’m really interested in having immediately are my primary keys, foreign keys, and maybe one more field to identify what that table is going to have. Okay. Because you can always come back later on and add additional fields and remove fields from that table. Really, what I’m concerned about initially is to have my primary keys, foreign keys, and then establish my relationships so that I know how my tables are linking back and forth. Okay. Structure is very important. Okay. All the other fields you can worry about later.

So e-commerce, what does the e-commerce example entail? Well, usually we have some kind of a table for products. We’re gonna have users. We’re gonna have orders. We might have reviews. We might have comments and really depends on what functionality you would like to have inside that e-commerce application. You may not have a need for a comments table, but if you’re trying to copy something that Amazon has, Amazon has a list of products. Each product can have many reviews and each review can have many comments. Okay. So right there, we have three different one-to-many relationships. Also. It doesn’t matter which table you start off with first. I always like to put together my user table first. Okay. So for this table, let’s build a user table initially. So we’re gonna have user ID. You can also call this customer ID.

Okay. You’re gonna have your own naming convention. Really just depends on what preference you have when it comes to naming your fields. For data type, I always like to go with random ID myself. Again, that’s a personal preference. A random ID is a unique ID that’s automatically generated, and this ID is gonna be used to uniquely identify each user inside this table. Right? So John will be having a specific ID. Sarah can have her ID. Ken can have his ID, so on and so forth.

And again, I don’t like to list all of my fields right away. I can always come back, but for now, let’s just have name, email, and password. Okay. Email is also gonna be a unique field and password is going to be a password data type. Again, when I’m building my tables, initially I have no need to list all of my fields first. I just need a few key fields and then I can save it. And let’s call this e-commerce TBL users. And there is my very first table. Okay. Now for the second table, let’s create a table for products. Our e-commerce application is going to have products, right? So let’s build a new table. And again, the very first field that you should always add to every single table that you create is always going to be the primary key. Make sure you put that at the very top of your list. It’s a mandatory field. Every single table must have it. So let’s have product ID. And again, for my primary keys, I always like to use random ID. Again, it’s just a personal preference. You don’t have to if you don’t want to. Again, it’s completely up to you.

Now. I need to link my products to my users. Let’s say your e-commerce application, you want users to be able to list products for sale. Okay? So not only is the customer buying the products, but you can also have the customer be able to post products that they’re selling. So in that case, I would list down user ID, insides field as a foreign key, because I need to be able to link my products to my users that are selling those products. Okay. And this becomes a foreign key. And for my foreign key data type, it’s gonna be a text 255. If you’ve attended my training sessions before, there are some database rules that you need to follow. If you are using random IDs as your primary key, random ID has a combination of characters and digits. And if you wanna stamp that random ID into a different table, you have to use text 255 data type because text 255 supports both characters and digits. Okay. And again, I’m not gonna list all of the fields inside its table initially. Let’s just have title of the product and maybe description. Description is going to be text 64,000. Later on, I can come back to this table and add my images, date fields, status of the product, or any other field that this table might have. All right, let’s save this. Okay. I cannot use description, so let’s write it out. Okay. Let’s save it. And let’s call this e-commerce TBL products.

And so far we have two tables. We need to build four more tables. All right. So now let’s link the reviews to our products because we noted a single product can have many reviews. Okay. So new table. Again, always add your primary key at the very top. Okay. And this is gonna be called review ID, and I’m gonna use my data type, random ID. All right. And underneath that, now we need to link the reviews back to our users. Okay. And we also need link the reviews back to our products. Okay. I need to know which users submitted the review for what product. So this table is going to have two foreign keys. We have the product ID, which is gonna be, again, text 255. And then we also have user ID, Text 255. So inside this table, we’re gonna stamp two foreign keys, one for product and one for user ID, because I need to know again, who the review belongs to from the product perspective and who the review belongs to from the user perspective, which one of our customers left that review for what product? And then we’re gonna have other fields inside this table, maybe, title of the review, comments, and, maybe, I don’t know, dates submitted.

We’ll come back to this table later on if we need to add additional fields. So this is gonna be text 64,000 and this can be a timestamp and that’s all we need for now. So e-commerce TBL reviews. Okay. So there’s my third table. Now let’s have a table for comments because each review can have many comments. So we’re gonna set up a brand new table here. And again, hopefully, now you know, we should be adding our primary key first. Comment ID, random ID. Anybody wanna let me know what two, foreign keys we need to track here? Let me know in the comment section, if you know. So we are tracking our comments for what other information from the other tables that we have currently built. User ID is one of them. Very good. We need to know which user left the comment. For. Each. Not product ID.

We’re linking our comments back to which other table? Review. Okay. Review ID. So this will have a foreign key review ID. So when you leave a comment, you’re leaving a comment for the review and we know which user left the comment. Okay. And now we can just add our other fields. Let’s have the comment itself that can be text 64,000 and date submitted. And that could be a timestamp. Okay. Let’s save it. So e-commerce TBL comments. Okay. Two more tables left to build.

Many-To-Many Table

Now, when we think about e-commerce application, it’s going to have a many-to-many, okay. Not only can one person purchase the product, many products. Okay. So imagine you’re on Amazon, you’re buying a Kindle, you’re buying a TV, you’re buying shoes as one user. Okay. But each one of those products can be ordered by many other users. So that is an example of a many-to-many, okay? One order can have many products and each product can appear in many orders. Okay. So it’s going back and forth. Okay. Many-to-many. A very classic example, almost every single e-commerce type application has a many-to-many setup. So what I need is I need a table of orders. Okay. So we’re gonna have order ID.

That’s gonna be a random ID. Okay. Inside this table, we also need to have a user ID or customer ID, because I need to know which user placed the order. And then for this table, what other fields can we list here? Let’s have maybe billing info and maybe shipping info for now. Now you can break that down into address, city, state, and zip. For now. I’m just gonna include two simple fields just so that I know the reference back and know that this table is going to contain order information. Okay. And that’s all this table needs to have. So let’s save it. Let’s give it a name. We’re gonna call this e-commerce TBL orders.

Joining Table

And a final table that we need to build is a joining table between them. Okay. So we need to have the table. That’s going to contain all of the orders and products, where we stamp the primary keys from both the products table and the orders table into this joining table between them. This is how we’re gonna be able tell one order has many products, but that product can appear in more than one order. Okay? So we need our sixth table and final table. And for this one, you can just call this maybe PO-ID, which is product order ID. You can have some other naming convention if you’d like, so we’re gonna have a primary key for that. And we need to have two foreign keys inside this table. We have the order ID And we have the product ID. And then maybe quantity.

Okay. And this can be a number and these are my two foreign keys. So let’s save it. Let’s give it a name, e-commerce TBL, let’s call this orders products. Now you could have additional tables if you had lookup tables for this application. But these are more or less the main tables that a typical e-commerce solution will have. Once you have all of the tables created, my next step, what I like to do is go into relationships.

And I like to connect all of my tables very quickly. So, I’m gonna include all of my tables here. We have table of users, reviews, products, and comments. If you haven’t seen this screen before, it’s just like a traditional database. If you come from an access background, it’s gonna look very familiar to you. You can expand and collapse tables. You can move the tables anywhere you want, however you want. And it really just depends on how you like to use this screen for your workflow. Some people like to work top-to-bottom. Some people like to work left-to-right. Personally, I like to work left-to-right in terms of how my tables connect to other tables. Now I need to see the name of my table. So let me just expand my tables a little bit so I can see the name of the table.

Okay. Just trying to figure out how to get rid of this highlight that I have in my table. Let me remove it and bring it back in here. All right. Okay. So let’s just expand all the tables very quickly and then we’re gonna see how we can connect them. And then once I connect my tables, this is gonna give me a very good idea in terms of schema and how all of my tables are linked together. So this is always my starting point. I build my tables. I don’t include all the fields in my tables. I don’t care about that for right now. The only thing I care about is that I have the right structure in place. I’ve normalized my tables and data, and I know exactly how all of my tables are linked. Okay. Other fields can easily be added by going back to the table design and including them inside a table, your primary and foreign keys, however, cannot.

Okay. So last thing that you wanna do is get to the point where you’ve built 10 data pages. And if you haven’t set up your tables correctly from the start, you might have to delete some data pages and you might have to delete some tables. Okay. And I don’t want anyone to get to that point because you’re gonna get flustered. I promise. Okay. So if I can give you some advice here, initially, always spend a little bit more time on table design to make sure you have all the tables built correctly because later on you can expand upon that easily and be able to include other tables if your application requires it. Okay.

Linking Tables

Alright, so let’s see how we can link all of our tables. Okay. So let’s move the products table down here. Let me start off with my user’s table. Usually, I’ll have my user’s tables on the left-hand side, and we have the ability here to join these two tables by clicking on the field first.

Okay. And just moving this line over to the other table and letting go, okay. This is gonna bring up this popup for relationship setting. Initially, I don’t really play around with the settings right away. Caspio is automatically going to identify the relationship type for me. It’s gonna be a one-to-many. Okay. So we’re gonna hit create, and it’s gonna show you that a single user, a single customer, is linked to many products, meaning that one customer can list many products, if they’re selling multiple products, one-to-many. Okay. Next thing that I like to do is let’s link the reviews to the products. Okay. We know that one product can have many reviews. Okay. So I’m going to drag the product ID, which is the primary key to the foreign key inside a reviews table. Let go, once again, you’re gonna see a one-to-many.

Now we also see that one user can leave reviews. Okay. So what I’ll do at this point, instead of going over my tables with the line, I’ll just reposition my table up and this one down a little bit. And I can also collapse this table a little bit more just to make room for other tables. And then I’ll join this line from the user’s table to the reviews table, let go, and hit create. So if we think about it here, this actually has a many-to-many relationship, okay? The user can leave many reviews for a single product, but that product can have reviews from other users. So that’s an example of a many-to-many where we stamp the product ID into the reviews table and a user ID into the reviews table, which is the joining table between these two tables. An example of a many-to-many.

Okay. Now we’re gonna look at one more example of a many-to-many, but before we do that, let’s link our comments table to our reviews. Because again, each review can have many comments. If you’ve ever left a comment on Amazon or read comments, you know that each review can have many comments. So let’s stamp the review ID over to the review ID field in the comments table. Let go. Once again, that’s gonna carry a one-to-many. And again, once we rearrange these tables a little bit, each user can leave many comments. So we’re gonna drag this line over to our comments table, let go, and hit create. The reason why I really like this screen myself personally, especially if you’ve worked with databases before, it really lets you know, how all of your tables are linked.

And what it comes down to, what it boils down to is these one-to-many and many-to-many relationships. And you have to have the ability to translate the functionality of the app, back to the tables and how all of your tables are linked. So if you have, let’s say, patients, visits, and doctors, that’s an example of a many-to-many, because one doctor can see many patients, but that patient can be seen by more than one doctor. So that’s an example of a many-to-many, and you will need to have three tables. So every time you have a many-to-many, you’ll need three tables. If you have a one-to-many, you will need two tables. Okay. All right. Let’s see how all of the other stuff that we have ties back into our application. So we have the user ID. We need to stamp the user into the orders table because we need to know which user placed the order.

So we’re gonna drag this line over to this table and let go. That’s gonna be a one-to-many. Okay. Now each order can have many products. Okay. So I need to stamp the order ID, enter the order ID foreign key inside the orders and products. Okay. Very simple. Hit create. And the final one here is we wanna stamp the product ID inside a joining table between them. So we’re gonna stamp product ID inside the orders and products. So let go and hit create. All right, once again, one order can have many products. Okay. And each product can appear many times in many orders. Okay. It’s example of a many-to-many.

And here’s your database designed for an e-commerce solution. Okay. Let me know if you have any questions based on what you’re seeing. This is a very simple setup. You just have to be able to translate the functionality into tables because once you have the tables built correctly in Caspio or any other database environment, it’s very easy to build all the other functionality on top of the tables. Okay. If you ask me, tables can be somewhat challenging if you don’t have a database background.

Okay. But just think about it this way. Every time you have a one-to-many, you need two tables, many-to-many, three tables. And all you’re doing is linking the tables back and forth between the primary keys and the foreign keys.

Highschool Example

All right. You guys ready for a more complex example? We’re gonna look at an example for high school now and how we can link our tables. And we’re gonna build 11 tables. This one has six tables. Let me know if you have any questions. But this is always my starting point. Okay. I’ll build all of my tables. I will link all of my tables. And then I’ll go back to my tables and add the remaining fields that I need. And this becomes the blueprint of the e-commerce solution that I’m gonna build in Caspio. Okay. Was that clear? Let me know if that wasn’t clear. Yeah. You can always review these videos. Each live stream is being recorded and it’s gonna be available on our YouTube channel. So you can always find the video and review later on. Absolutely. All right. Let’s take a look at something a little bit more complex.

So we’re gonna call this EDU High school design. I don’t know, just a very arbitrary name. Don’t pay too much attention to my names. We’re gonna hit finish. We’re gonna open and, again, go directly into our tables.

So if we think about the design for, let’s say, a high school setting. You can choose to have parents have a login to be able to review their kids’ grades, attendance. You’re gonna have classrooms. You’re gonna have grades. You’re gonna have courses. You’re gonna have exams. There’s some many-to-manys here as well, because not only can a single classroom hold many students, but that student can go to a different classroom as well. When I say classrooms, I’m thinking about classroom numbers, like 83a or 22b or something like that. Then we’re gonna have grades. When I talk about grades, I don’t mean exam grades and scores. I mean, ninth grade, 10th grade, 11th grade, senior year, 12th grade, et cetera. Then we could have courses because each grade will have multiple courses. You could have ninth grade have geography, bio chemistry, 10th grade can have AP history and all of those other courses, right? And we want to be able to track exams. It could be a type of an exam. It could be a midterm, it can be a quiz, it can be a final exam. And then you have the name of the exam itself. And then finally you have the test scores where we track two foreign keys, actually the course ID, the exam ID and a student ID. Okay. And of course all the scores marks, you know, did it get an A, B or a C for what student, what course, and what exam?

All right. So let’s begin by maybe including a parent table. Let’s say our application is gonna have the ability for parents to log in. So parent ID is gonna be the very first field. Underneath that, again, just list down just a few fields initially. You could have name, you could have email and password. Email field is always unique and password field going to be a special data type. We want the parents to be able to log in and see their students’ accolades or some other things that they’ve done inside the classrooms. You know, grades, exams, all that good stuff. Save, and we’re gonna call this EDU TBO parents. All right, next table we’re gonna have is students. We also want the students to be able to log in and be able to see their grades. So student ID, Now we need to link our students back to our parents. Okay. So when a parent logs in, they only see information pertaining to their kids, right? So we need to have the parent ID as a foreign key inside this table. And then for students, why don’t we also have the name, email, and password?

So email is gonna be unique. Password is going to be a special data type and let’s save the table, EDU TDL Students, All right. Let’s have our third table, which is gonna be the teachers. So we’re gonna have teacher ID. Of course, we need to have the teachers be able to log in too, so they can see the attendance. They can see their students and they can also post a score for exams, right? So this is going to be a random ID. And then we have again, name, email, and password. Unique and password and save. So let’s call this EDU TBL teachers. All right.

So what’s the next table that we can build? So let’s build a table for classrooms. Okay. Because each teacher needs to be linked to many classrooms. It’s gonna be a one-to-many. Okay. The teacher can teach from 22b, but he or she can also teach from 22a. You can have different classrooms. They don’t have to belong to a single classroom. Okay. So let’s have a table of classrooms. So classroom ID, Again, that’s gonna be a random ID.

We need to link our teachers to our classrooms. That’s gonna be text 255. Now inside of classroom table, we’re also gonna have another foreign key, which I’ll explain later on, which is gonna be the grade ID. Okay. Which is gonna be a one-to-many between the grades. Each grade will have many classrooms, right? So ninth grade will have many classrooms. 10th grade will have many classrooms. 11th and 12th grade will also have many classrooms. Okay. So I’ll explain this foreign key a little bit later on, because I don’t have the table for grades built yet, but we will in just a minute, that’s gonna be the very next table. All right. So grade ID. Let’s have the year and maybe some remarks for the classroom or name of the classroom, things like that. So we’re gonna call this EDU TBL classrooms.

All right. Before we build a grade table, let’s build the joining table between the classrooms and the students. Okay. So that’s gonna be a many-to-many, that’s our third table that’s going to join the classrooms with the students, right? Because not only can one classroom hold many students, right. But each student can go to more than one classroom, right? Spanish class could be 22b. English class could be 22a. They’re walking around the campus. They’re going to a different classroom. Right. So let’s build that joining table between them. So for my primary key, why don’t we call this classroom student, CS ID. That’s gonna have a random ID. And now this table needs to have two foreign keys. Anybody want to guess?

Random ID VS Auto Number VS GUID

Yes. I always use the random. Wouldn’t student ID be unique? Yeah. Did I not make it unique? I’ll go back to my table in just a moment. Thanks for catching that. I’ll go back. Maybe I forgot to change my data type inside a student’s table. Good catch. Okay. I’ll go back in just a moment. You always use random ID because other videos have auto number. Yeah. I recommend using either random ID for the primary key or GUID. They’re much longer IDs and much more secure. Okay. Auto number, I usually use if I have a simple lookup table. I’ll use auto number for lookup tables, but for my main tables, I always like to use random ID or GUID, leaning heavily towards random ID, because that’s just what I’ve used for a very long time. Now, before we introduced these other ID types, random ID, GUID. Back then years ago, Caspio only had auto number. So I think that’s why some of the videos only use auto number, I was used to using auto number for the longest time. But now I’ve switched over to random ID.

All right. So for this table, we’re gonna have classroom ID. Which is gonna be the foreign key. And the student ID will also be the foreign key. Okay. And then, this table doesn’t really need to have any other fields. I think these are really all we need for this table. So we’re gonna go ahead and save this and call this EDU classroom students. Let me go back to my students table. Somebody pointed out here that I did not make it a random ID. Good catch. Thank you. So that needs to be a unique field. Thank you. Let’s go back to tables. And the next table that we can build is actually the great table. Okay. So let’s have the great ID.

Let me make sure I select my random ID this time. Okay. By the way, how you’re gonna be able to catch the fact that you didn’t make that random ID. For my student ID, remember how I left it at text 255? If I went to my relationship screen, I wouldn’t be allow allowed to join those lines. That would signal to me that, hey, you didn’t make that a unique field. So go back to your table, make it a unique field, and then you’ll be able to join those two tables. Okay. So then we have the name of the grade and maybe description, which can be text 64,000 and let’s save it. Let’s give it a name, EDU TBL grades. All right. Next, let’s build the courses. Now courses are gonna tie back to our grades table. It’s gonna be a one-to-many, okay, because each grade can have many courses. Okay. So we wanna break that down as well, right? In ninth grade, you can have many different courses. In 10th grade, you’re gonna have different courses,.11th, 12th grade, all gonna have different courses. And they’re not gonna be the same as the ninth grade, 10th grade, 11th and 12th grade.

We’re gonna have course ID. Once again, always the primary key. I know I keep repeating myself, but that’s how I learn, just the repeat in my head. Primary key always goes at the very top. Then we’re gonna have grade ID, which ties back to the grades table. And then for the courses, we could have the name and description as well. So name of the course and description of the course. Text 64,000. Save it. And let’s call it EDU TBL courses. How many tables do we have? We have seven tables. So that means we need to build four more.

So the next one that we’re gonna build is the attendance. Now attendance is simply going to be linking back to our students table. So we’re tracking the attendance of each student. Okay. This is a very simple table. It’s a one-to-many. So attendance ID, Random ID. Back to the student’s table. So that’s gonna be student ID. That’s the foreign key. And then maybe you want to be able to track this with, let’s think about this for a second. Maybe just a status. Status is good and you can have this be a check box. So if this student is present, they’re attending the class. You can just check it off and that’s gonna be inside the attendance table. Let’s make sure this is correct. Yeah, it looks good. Save it. EDU TBO Attendance.

All right. Three more tables to build and we’re gonna be done with this database design. And for this one, we’re gonna have all about the exams. Okay. So we need the exam type. We need the exams themselves and we need the exam results. All right, let’s do this very quickly. So let’s start with the exam type. So ET exam type ID, that’s gonna be the primary key. Let us choose random ID. And then for exam type, we could have just the name of the exam. You know, it could be a midterm, it could be a quiz. It could be a final exam. These are all the different types of exams that we’re gonna have. Actually, maybe it’ll be better just to call this exam type ID. Let’s do that. Save it. EDU TBO exam types. All right.

Our tenth table are gonna be the exams themselves and the exams are gonna tie back to the exam type, right? So if the exam name is, I don’t know, fall 2022 midterm, right? What type of an exam is that? It could be a quiz. It could be a final exam. Okay. So exam ID. It’s gonna be a random ID. Exam type is going to be the foreign key. And then for your exam itself, you probably could also have the name and maybe the start date of the exam.

So that’s gonna be a date field, and let’s save it. Give a name EDU TBL exams. And one final table left is just the exam results. Okay. So we now need to be able to track two foreign keys, sorry, three foreign keys inside a final table, the student ID, the exam ID and a course ID. Okay. So let’s do this. New table. So we’re gonna call this exam result ID and that can be a random ID.

So we have the student ID. We have the exam ID and we have the course ID. And then you can have your grades, your scores, whatever you decide to call that. Maybe a mark or marks. And that could be a number or it could be a different data type depending on the localization or the region. So if you live in Europe, they use a system of one through five. If you live here in the United States, we have F through A. Okay. So it’s depending on the region and how you want to be able to track those mark and scores. So let’s save this table now, and let’s give it a name, EDU TBL exam results.

Guys, let me know if this is helpful. Okay. Hopefully, I know it can be confusing when it comes to database design and how tables are linking back and forth and what fields you need to have. But again, it makes sense once you get into the relationship screen and start connecting your tables, that’s when you actually can see the visual and the blueprint of the app and how everything links, but you gotta be able to know how to break down these tables depending on the use case and functionality. Okay. Some examples are more complex. Others are a little bit more simple. It just depends on the scope of the application and how many tables it might have. Sometimes tables have 15 to 20 tables. Okay. But again, it all boils down to one-to-many and many-to-many. Okay. So let’s go into our relationships now. Okay. Yeah. Thank you so much. Thank you for the feedback.

All right. Let’s see how we can link all of our tables together. Okay. And then we’re gonna get into our final use case, which is project management. Many of us can relate to it and I think project management is going to be a little bit more relatable, because most of us have used a lot of project management tools out there.

Organizing Tables

So let’s begin. I always begin with my user tables. So we have the parents, we have the students, and we have the teachers, okay. I’ll move all these tables here to the left so that we can see them.

Also one recommendation that I have, you don’t have to do this way if you don’t want to. I always like to put my foreign keys underneath my primary keys. Some people have a preference of putting all of their regular fields and then they put the foreign keys at the very bottom of their tables. That works just fine too. But the reason why I like to put foreign keys underneath the primary keys is because there’s so much wasted space here. I don’t really need to see the regular fields in my tables. Really, the only fields I’m really interested in seeing here are the primary and foreign keys because now I can collapse my table and that opens up so much space for other tables that I can include in this window. Now you’re gonna be able to scroll up and down if you have even more tables, but it’s just cleaner in my opinion. But again, that’s just my opinion.

Linking the High School Example Tables

Okay. So we can begin by including our attendance. That’s a very simple one-to-many, okay. That joins our students to our attendance. Okay. So you can see the student ID is going to link back to the student ID in the attendance table. Oh, that’s gonna be a very simple one-to-many. Each student can attend the class as many times and we’re simply just tracking the attendance. All right. There’s also relationship between the parents and the students. You can see the foreign key ID here down below. Okay. So we are just going to drag this line over to the parent ID and let go. Each parent can have many students or many kids. All right. Very simple.

How Do You Know Which Tables to Make?

Let me take a look. And when do you recommend, it’s a question, when do you recommend to make a new table rather than to try to load up a table with a bunch of fields? When do you recommend to create a new table? So before I begin building my application in Caspio, I already know, at least for the initial version of my application, that’s gonna go live, I know exactly the use case and the workflow that my application it’s going to have initially when I want to go live, okay. Once I have that in mind, then I will build my tables right away. I’ll build all of my tables. Okay. I won’t even go to this relationship screen until I’ve built all of my tables first. You can, if you want to, you can break that rule. That’s fine. If you, let’s say, you build two or three tables and you feel more comfortable coming here and connecting your tables, you can do, you can go back and forth. That’s perfectly fine, but I’ve gotten used to just building my tables first, all the tables that I’ll need to have, then I’ll go to this screen and I’ll link all of my tables using these lines. That’s just my own way of doing it. Okay.

Hopefully, that answers the question. I mean, if I could, if you want me to make a recommendation, I would recommend that you build your tables first and then you join the tables using these lines. But I understand that a lot of people don’t understand database design and for some people it’s easier to build two tables, connect them, go back to tables, build the third table, come back here, connect third table, and then just connect one table at a time. Yeah. So don’t start making tables on the fly. Okay. You should at least know what tables your application initially is going to have. So you have an idea of what you want to go live with. Okay, the beta, what’s gonna go live initially, and those tables need to be set up correctly, but they need to be set up correctly in order to make room for other tables in the future.

All right. Sounds good. All right. What other tables do we have? So we have the classroom. Okay. So here’s my classroom’s table. Now the way classroom is going to link back us back to our teachers. So let me move this table down. We might have to rearrange these tables later on, but for now we know that a single teacher can belong to many classrooms.

Let’s also include the grade. We can include that right now. So let’s include the grades. Where is my grades? Right here. Let’s move that down. Okay. So one grade can have many classrooms. That’s a one-to-many. Okay. Hopefully, everyone here has been to high school and understands the workflow here. Like ninth grade, 10th grade can have many classrooms. Okay. That is going to be a one-to-many into the classrooms table.

Okay. What other table can we include next? Well, let’s include the courses table. Okay. A single grade can have many courses. Okay. That’s a one-to-many. Okay.

Hang on one second. This is supposed to, not to that field. Sorry. Wrong field. That needs to tie back to my grade ID field. There we go. That’s better. Okay.

Let’s now include the many-to-many relationship between the students and the classrooms. I’m just gonna move this, collapse this, a little bit. Let’s move this here. So I need to stamp the student ID into the classrooms. The students that’s a one-to-many, and we also want the classroom ID to stamp into that table as well. And now this table becomes a many-to-many between the students and the classrooms. It’s a joining table between them. Okay. because we can track. Many students can belong to one classroom, But that one student can belong to more than one classroom. So many students to one classroom and one student to many classrooms. Okay. So many-to-many. All right. And the final three tables that we have are the exam tables. So we have the exam types. So let’s include that to our exams. Here it is over here to the right. So we’re gonna join these tables together. Let me come over here. Exam type. All right, let’s try that one more time. There we go. So it’s a one-to-many. A single exam can have many different types of exams. Right? We have quizzes. We have midterms, we have final exams, et cetera. And then we have the exam results. So let me move that table over here.

And inside this table, we’re gonna have three different foreign keys. So we need to track the exam results for our students. So we’re gonna drag the student ID over to this table, join that. For what exam? We’re gonna join that. And for what course? We’re gonna join that.

Okay. And now you can just slowly start rearranging these tables however you want, whatever makes sense to you. Just gonna move them to the left a little bit. Again, I like to work left to right. A lot of people like to work top to bottom, but this is just my own preference so that I can see all of my tables inside my schema and how all of the data is linking back and forth. All right. And from here you can just save the layout. Okay. Any questions on this database design? I know it’s a little bit more complex, but ultimately that’s what you’re gonna have to do. You know, you gotta learn how to…

Additional Relationship Settings

Okay. So go over additional relationship settings in the live session, please. If possible, enforce referential cascade delete. Okay. Sure. I can briefly talk about that very quickly. It’s actually very simple. So for example, let’s say you’re looking at the relationship between the students and, maybe, the attendance. So let’s take a look at this relationship.

Okay. So if you enforce referential integrity between these two tables. Okay. If you try to delete a student and that student has many entries in the child table of attendance, you’re not gonna be able to delete that student. Why? Because that student has linked entries in the child table, in the attendance table. So it’s a guardrail. Think about it that way. If you enforce the referential integrity between these two tables, however, if you leave this unchecked, okay, and now you delete that student and that student has many entries inside the attendance table, you’re gonna leave a lot of these records orphaned. They’re not gonna belong to anyone now because you deleted the primary key from the parent table. So usually when I build my applications, typically I will enable this check box. I will enforce my referential integrity just as a guardrail to prevent me from accidentally deleting some entries from the parent table if that entry has linked entries in the child table.

Referential Cascade Delete

Okay. Cascade delete, be very careful with this checkbox. Okay. I only use this checkbox. If I’m trying to purge a lot of data or delete a lot of data at once, because now if you delete a student from this table and let’s say you have 10,000 entries in the child table that belong to that student. Once you delete the student, you’re gonna delete all 10,000 entries instantly from the child table as well, if you enable cascade delete. Okay. So again, be very careful with this checkbox. I almost never have that enabled. Okay. I may temporarily enable it if I’m deleting a lot of data. And a cascade update, very simple, kind of like cascade delete. If let’s say you’re stamping the student ID 10,000 times inside a child table and you happen to update the student ID in the parent table change will propagate throughout the entire child table as well. So now all 10,000 entries inside this table will have that same ID that you just changed in the parent table. Okay. Sometimes I will have this enabled in my applications. Very hard to find a use case when you can enable this, but it’s possible.

Hopefully, that helps. Let me know if there’s a follow-up on that question. Is Amazon based on basic tables like this? Well Amazon probably has a lot of tables, especially lookup tables. I’m sure they do. But yeah, I mean, it could be as basic as what we created today. Okay. But you have to remember, they have a lot of developers working on Amazon, so I don’t know how intricate their database design is, but if I had to guess they have those main primary tables that we built today, including additional lookup tables, probably many, many lookup tables, because they’re most likely tracking a lot of things in the database. Okay. Can you build an Amazon with Caspio? Yeah. You can build a basic rudimentary version of Amazon with Caspio, but if you have development knowledge, front-end knowledge, then yeah, you can get as close as possible to Amazon. But you have to remember. Amazon has been around for a long time. They have a lot of developers. So a lot of time went into creating all of those workflows on the front end and also on the back end.

Project Management Example

All right. Let’s go into our final use case that we have for project management. Hopefully, you guys are finding this helpful. This is really what it comes down to when it comes to database design. Okay. So let’s go back to the homepage. Let’s save this layout and now everyone’s gonna be able to relate to this one. It’s a project management example. So let’s call this PM design for project management. We don’t need an underscore.

Open it, go to our tables, and not all project management applications are the same. Some could be simple. Some could be more complex. In my example, today we’re gonna have 11 tables. Okay. And obviously, we need to have projects. We need to have tasks. We need to have clients, users, and milestones, costs, hours. We need to be able to track all of that information. So again, I always start with my users table. So we’re gonna have users, so user ID And we’re gonna do random ID for that. And let’s have name, role, email, and password. All right. So email is unique. Password is password. Again, you’re gonna have other fields, maybe title, department, phone number, address, whatever information you need to track for your users. Okay. Let’s save the table and call this PM_TBL_users. All right, let’s go with our second table. Second table. Let’s have clients. Okay. Because each client is gonna be linked to our projects.

Okay. So let’s have client ID. That’s gonna be a random ID. Let’s have the name of our client. Maybe you want your client’s website? I don’t know what other information could we list very quickly. I think that’s good enough for now and let’s save it and let’s call this PM TBL clients. You could also have the clients be able to log in too. I didn’t include those two fields in here, email and password. But if you want your clients to log in and check the status of the project, you can do that. Okay. So we’ll hit, finish and save.

Next table is contacts. Each client can have many contacts, right? So think of almost like a CRM example, right? One account can have a company that has many contacts. In this case, we’re gonna have clients that have many contacts, so contact ID with client ID. And my contact ID needs to be my primary key. And then for our contacts, we could have name. We could have email, maybe gender, maybe title, all that good stuff. PM TBL contacts. All right.

The next table that we’re gonna build is the projects table. This is gonna be one of our main tables that our application is going to have. So let’s have project ID. Obviously, that’s always gonna be the very first field. Project ID, random ID. And now inside the projects table we’re gonna have, actually we’re gonna have three different foreign keys. Okay. We want to have the manager ID. So we’re gonna have project manager ID.

That’s gonna be text 255. Anybody want to guess the other two? Well, it’s not fair for me to ask you that last one, but what’s another one that we can track inside a projects table? What are we linking our projects to? To who, in this example? And we already built that table. It’s one of the tables that we already built. Projects that are linked back to. So that’s the user actually. I just renamed that field to project manager ID, but that’s essentially the user ID. So that’s correct. That’s one of them. I could also call this user ID if I wanted to. But I think when I build my project management applications, I always like to name that field this way so that I know who the project manager is, which user ID. Client. Yeah. Very good. So client ID, thanks Peter. And we’re also gonna want to have status ID and that’s gonna be our third foreign key because each project will be able to have many statuses. It can be a new project, in progress, canceled, archive, deleted, all that good stuff. And then let’s have project title. Maybe project description, which can be text 64,000 and whatever other fields you need to have, maybe start date of the project budget, all that good stuff. Okay.

Can you have multiple users asigned? Yeah, you could, you can turn this into a many-to-many if you wanted to via third table. But I think for each project, usually there’s one project manager and then you have multiple tasks. Okay. And each task is assigned to a user. I’m used to having that project management application where we have a single project owner. Okay. And then each project will have many tasks and each task will be assigned to somebody else. Or one person can handle two tasks if you so choose. All right, let’s save the projects table. So PM TBL projects, and then let’s have the project status. So just a very simple ID. That’s gonna be random ID And then the status itself. Save it. And this is gonna be called, PM underscore project status. Okay. Day is our fifth table.

Let me take a look at my question. This is gonna be the last question I’ll handle for now. We’re gonna have time for questions at the end, but I have two more minutes and I have to build six more tables very quickly. So just bear with me. Let me take a look at my question very quickly. Do you have a use case for many-to-many table because in live stream, all the tables are linked one-to-many followed by a third table. So really a many-to-many is two, one-to-manys. Okay. So if you really think about it, a many-to-many boils down to two one-to-manys, that’s why they all look like one-to-many. So Caspio, when that popup window shows up, it’s gonna say one-to-many. You’re never gonna be able to read many-to-many. Okay. So all a many-to-many is, is two one-to-manys. Okay. But that joining table that has two many connections, that table itself is an example of a many-to-many right. Between the two other tables. The joining table between them is the third table.

Yep. So if that popup always says one-to-many, totally fine. Okay. It’s never actually going to read many-to-many. All right. What’s our next table. So let’s have the task table. So we’re gonna have task ID, obviously in this table, we need to be able to link our tasks back to our projects. So this table is going to have project ID because a single project can have many tasks. Okay. Now who are we assigning the task to? So we need employee ID Or user ID in this case, but that’s fine. You can call it employee ID. That’s totally fine. We also want to have the status ID because each task will have a different status. So that’s gonna be status ID. And we also want milestones.

Because each task can have many milestones. Okay. You don’t have to have that if you don’t want to, but I’m going the extra step here. Where I break down each task to have many milestones, one project, many tasks, one task, many milestones. Okay. So it becomes a chain effect, like one milestone can have many of something else, if you want to. Okay. Let me see any other fields in this table. No, let’s just have the title of the task and maybe task description to finish off just some basic fields. But you can see we’re actually stamping four different foreign keys inside the task table. So let’s give it a name PM TBL tasks. And now let’s say the task status table, which is a simple lookup table. So ID, All right, we’re gonna do random ID and status itself. Now, if you’re task lookup table for status is the same as your project lookup table, you could use the same lookup table for both, but there’s a very good chance that your task status could be different than your project status. So we create two different lookup tables.

And we’re gonna call this PM TBL task status. All right, what’s the next table that we can build? Well, let’s build a milestone. So milestone ID, This table is going to link back to our tasks. So we need the task ID, which is gonna be text 255. We also want each milestone to have its own status. Right? So again, this is gonna have status ID as well. And then we’re gonna create one more lookout table for our milestones. Let me think for a second. This also needs to link to the project as well.

Let me think, what else milestone is linking back. I don’t think we need to link back to our task here for milestone. I think what I’m trying to do is a milestone for the projects. Yeah. Let’s not link back to our task table. Milestones really need to be linking back to our projects in this case, if I’m not mistaken, I think, yeah, that’s the way we’re gonna do that. So let’s have the name of the milestone. Let’s have description. So milestone description, that’s gonna be text 64,000. Any other information we can track on the milestone? Let me think. You could have deliverable, you can have due date. Those are all very typical fields that you might have. So we’re just gonna do that to be a timestamp and we’re gonna save our table and call this PM TBL milestones.

Okay. We have, I think we have have three more tables to build, so we need that lookup table for milestone. So that’s gonna have status, random ID and PM, TBL, milestone lookup. Very helpful to have a lot of lookup tables In your applications that way, when it comes to dropdowns, you don’t have to create custom values all the time. You just link that to your table. So don’t worry if you have like five, 10, look up tables. Very helpful and easy to build those tables once. And then you can just reuse those tables in your dropdowns and things like that. All right. Two more tables left and we want the table for cost and for a number of hours on the projects. So we’re gonna have cost ID. I’ll change that to random ID in just a moment. Let’s have project ID, Let’s have milestone ID and any other foreign keys that we need to track into our cost. I don’t think so. So let’s have the name. You might have price times quantity, And I actually teach this in my training classes to get the total. So for price is gonna be currency for quantity is going to be number integer and total going to be a formula data type. Now for the formula data type, very simple equation here, we just need to insert our price with the times attribute here and quantity, and that’s gonna give us the total. So when you submit this form, you capture the currency, the price, the quantity, and then in a table, you’re gonna calculate that value. This is gonna be a random ID.

And these are my two foreign keys for cost, right? We’re tracking the costs for the projects and also for milestones. So let’s give this a name. We’re gonna call this PM TDL cost. And then number of hours as the final table. So we’re gonna call this our ID as the primary key. We’re almost done and let’s have the task ID. Let’s have the project ID And the employee ID. So which employees logging in the number of hours for what project and for what task. And then let’s have what other information can we have for hours? Date. We could have time. And maybe work completed, which can be a checkbox or text field. Time, let’s have that be a number. Date and time can be a date and time and are all good. Let’s save it. And let’s give it a name PM_TBO_hours. Okay. So there are all 11 tables and we have exceeded our one hour limit, which is fine. I’m just gonna show you how to connect the tables and then we’ll be done with our live stream today. So let’s go into relationships.

Linking The Project Management Example Tables

I know it can seem very confusing when it comes to primary keys and foreign keys, but it gets easier once you get the hang of it and how things link back and forth between your tables. Okay. I promise it gets so much easier. We have clients and we have contacts. So let’s link these two tables. All right. So one client can have many contacts. Okay. Let’s create those two tables, connections between them. All right. Let’s include our user table, which is our employee table. Okay. Let’s have the table of projects. Now, for project manager ID, that’s really my user ID field. Okay. But I renamed that field inside this table so that I know who the project manager is going to be. So I’m just going to link that ID to my projects table and let go. One-to-many. All right, let’s see our other tables here. Let’s have the task table, By the way, once you have this table here, you can also link your client to the projects. Okay? So let me move these two tables a little bit. Let’s move this to the right. Let’s move this here, move this down. And client ID is going to link back to my client ID. I don’t know why it’s not linking. Let me try that. Strange.

One client can have many projects, right? Let me also include the status. So for our project. So where is the status projects table? Task project status. Let’s include that table here. Very simple table. Okay. Let’s link that back to our projects. So ID goes here because a single project can have many statuses, right? And let’s see, what else can we do here? Anything else with the current tables that I have here? So we have a table of tasks. So let me link my projects to my tasks because a single project can have many tasks. Okay. We also wanna know who the task is assigned to, to what users. So we’re gonna link back to our users table like this. Okay. Can move that to the right a little bit. And now task can also be linked to statuses. So we have a look up table for tasks. Let’s include that. This is a very simple one. Link that here. Create. Sorry, wrong one. That needs to link to my status. So right over here, click on create. Okay. Let’s include our other tables. We have milestones. So milestone will have its own status. So let’s include that look up table as well. So that’s gonna link right over here. Okay. Each project can have many milestones. Okay. So let’s link the projects table over to our milestone table. Whoops.

Okay. Each project can have many milestones. All right. I think I’m good for that for now. Let’s include other tables here. We have the hours and we have the cost. So hours are gonna link back to our tasks. So how many hours are required to create the task or to finish the task? So let’s link those two tables.

Let me see if I can move these two tables down here or just switch my tables so it’s easier to line them up. All right. There we go. Okay. That’s a one-to-many. Let’s see. So this also links back to our project. All right. So we get the total number of hours it took to complete the project and we can also have the total number of hours it took to complete the task. So we can do the calculation later. So this is gonna link back to our projects.

And then we also need to know which employees logging in the hours. So that’s gonna link back to our user table. Okay. And then we have one more table, which is the cost. So that shifts things down a little bit. Let me just collapse the table so that we can see it. So the cost is gonna link back to our project. We need to know the cost for the entire project. You know, how much is it gonna cost? So let’s link our projects table back to our cost table for our milestones. So each milestone will have its own cost. And I believe that’s really all there is to when it comes to this project management application, this is how you link all the tables. I know it looks like a giant spider web, but believing that is the expected behavior. At the end, once you’re done linking all of your tables together, that’s really what it’s going to look like at the very end. Okay. Especially if you have many tables that are linking back and forth between other tables.

But now you can track… The question that I had earlier when it comes to many-to-many, once you have two one-to-manys like this into a third table. Now this table here, this connect here is an example of a many-to-many, okay. This table is also many-to-many between the projects and a milestone status. So this kind of becomes a many-to-many as well. So two one-to-manys into the third table, joining table, between them is an example of a many-to-many, three tables. And then you can have a simple one-to-many like this one here that we have, which is client can have many contacts. That is an example of a one-to-many.

So again, I know it can be somewhat challenging to do this, especially if you don’t have a database background, but hopefully, this was helpful, the livestream today. Thank you guys so much for attending the livestream this week as well. If you have any questions for me now, I’m happy to answer your questions. Let me know. But if I was building my project management application, and if I had this use case, this workflow in my mind, this is how I want to go live with my application. This is how I would set up all of my tables and how I would link all of my tables. Because now you can track and see, you know, one project can have many tasks. Okay. One user can be linked to a task. Okay. Projects belong to clients. Each project can have a status.

Yeah. So if this was a little bit confusing to some of you, I apologize. I was trying to pace myself with the time that I have, because I had three different use cases. So I was kind of speeding up in some places, but the video is recorded. It’s gonna be available on our YouTube channel. So if you guys wanna review and watch at your own pace, you can definitely do that at a later time. Okay.

Next Week’s Livestream Topic

For our next week live stream, we’re gonna talk about Google Map integration, which came up in my previous live stream. I thought it was a really good topic and we don’t talk about that often enough. And I think it’s a lot of fun visually to be able to see the pins on a map and what we can do with that in terms of plotting the locations using geocode locations and a zip code, if we have time and how we can change the pins based on a different criteria.

When Would You Have Two Tables Versus One for Contacts and Clients?

Let me take a look at my question here. So clients and contacts, all right. These two, some of your videos have one table and a contact is labeled client. When would you have two tables versus one for contacts and clients? So contacts and clients, help me out. Let me know what video I’ve actually joined these two together. Usually, clients can be contacts too. Okay. Maybe a better way to break this down would be company-to-contacts instead of clients-to-contacts. Because the name is very similar between these two tables. So I might actually do company-to-contacts as opposed to clients, you know, like client Google, I could have any contacts at Google. Right. So think about it that way. More like a company-to-contacts as opposed to… Because, yeah, they are very similar, my naming that I’ve used for this example. Yeah. Thanks for that. Hopefully that clarifies it.

So who has a need to build a project management application that’s this complex? To make them clients? Okay. So you have your users that can sell to customers, I guess, is what I would… We have contacts that sell insurance to make them clients. So you have prospects and eventually those prospects become clients or customers and you have some kind of an internal sales team that sells to them.

Caspio Demo Calendar

Can you do a session on calendar appointments, scheduling app, for example, a public submission form for daytime picker, similar to Caspio Demo Calendar. Caspio Demo Calendar. Which one is that?

Are we talking about, let me see. Where is it? App templates. Are we talking about the appointment scheduling app that we have? That’s only one that I can think of that has scheduling appointments on a calendar. So let me know which one that is. Caspio Demo Calendar. On your homepage. Oh, on my homepage. So let’s take a look. Save. My layout.

Caspio Demo Calendar. Caspio Demo Calendar. Caspio demo. Are we talking about this homepage or Caspio’s homepage? Let me know, please. On Caspio’s homepage. So let’s go to our homepage. All right. So are we gonna be looking through the video here that we have in the beginning or is it somewhere down if I scroll down, just let me know, take your time. We’ll find it.

But yeah, we can do a session later on on calendars for appointment tracking event management. We can definitely do something simple because we only get one hour. I did have a few other topics lined up for the next few weeks. But I can definitely add that to the funnel and to our list later down the line. I’m thinking maybe sometime in October. Try Caspio free or schedule it. Oh. So this is the… Who do we use for this? But you want it to see something like this? Where we have a list of times we have a calendar and then we can book an appointment. So once I book that appointment, we can have that be visible on a calendar. We can have it be displayed or we can where when somebody books, the appointment that time slot is no longer available for somebody else to book. Do me a favor.

Here’s my email in the chat window. Okay. Just send me a quick workflow. What you’re thinking. Okay. Send me an email and then I can email you back and let you know when we can squeeze that into our live stream. Remember I only get one hour. So if you send me a long workflow, I might have to remove some things so that we can squeeze everything into our demo. Okay. But send me an email. I’ll be happy to look at it and let you know what we can address in our live stream. But that is a good topic as well. Okay. Yeah. You’re welcome.

Let me see if there was another question. We have users. Agents. Yeah. Agents. Okay. Good. Who call contacts? Who they convert to clients, correct? Yep. Perfect. So agents are like the sales reps, who are cold calling clients. And initially, if the client doesn’t convert, they’re initially a prospect. We can add ’em into the database and then you have a status to convert to a prospect, to a customer. So that would be a simple dropdown. Yeah. And then you can obviously like a CRM you can track where one contact or prospect. We can have many logs. Right? We have email logs, we have phone logs. Those are all standard inside this type of workflow. It’s just a few one-to-manys, right? So one contact can have many logs, many email logs. That’s a one-to-many, or you can have multiple phone conversations again. That’s a one-to-many because you treat each log separately.

All right. Let me know if you have any more questions again, guys. Thank you so much for keep coming back to my sessions. I really do appreciate that. Like I said, next week, we’re gonna do map mashup, which is exciting, because you’ll get to see some nice visuals and how we plot these pins onto a map. So if you do have a need for something like that, we’ll talk about that on Monday. And for this example, I will give you the finished application before I show you how I did it. Okay. So that you know exactly what we plan on developing in that course or in that live stream.

Conclusion

All right. So I will go ahead and end this stream on my end. I hope you found today’s live stream beneficial and informative. Again, you’re always gonna be able to watch this at a later time. It is available on our YouTube channel and you can see how I go about building my tables and how I link my tables back and forth using the primary keys and the foreign keys. Believe me, once you have the table structured down correctly, initially everything else will just flow much easier. Okay. That includes authentications, views, and especially data pages. Okay. So thank you guys so much. I appreciate the feedback as always. That’s very helpful so that I know what I can improve upon and what I need to fix for the next live stream. But sometimes in realtime we have to make adjustments in these live streams, depending on what questions I get and the way we want to take the live stream. All right. Thank you guys so much. Have a good day. Stay safe out there and I’ll see you next week. All right. Take care. Bye bye.