2.2 Designing the Ticket Table and Model
Our app is a helper for support desks and revolves around the concept of "tickets". So let's design a table and data model to represent tickets.
1.Introduction1 lesson, 01:24
2.The Application's Front-End8 lessons, 1:06:02
3.Ticket Administration6 lessons, 56:09
4.Conclusion1 lesson, 01:13
2.2 Designing the Ticket Table and Model
We're going to start by defining our tickets table, because our entire application is going to revolve around this table. So we will start by using artisan to make:model. We'll call it Ticket, and we will use the -m flag to create that migration, because we need that migration. So let's go to database > migrations, and let's open up that new migration file. Now, when it comes to the fields of this table we need to decide how we are going to handle everything, because we're going to have users that submit tickets, then we are going to have users that will manage those tickets, and ideally in a real world application, everybody would have to login. So whoever's going to submit a ticket would have to register, login, submit their ticket, and they would also have to be logged in to view their ticket, however, then we have to get into managing users, and all of that stuff. So we're going to keep things just a little simple, and we're not going to worry about that. Instead we're going to keep track of the submitters email address, and they will need to supply their email address, as well as a unique ID for the ticket in order to view the ticket. That way they don't have to log in and we can still have authentication for our technicians and the people managing our tickets. So let's have a string that we will just call email. Let's go ahead and add a length here. 255 should be plenty, if not then, well, [LAUGH] there's problems there, and then we need a subject, or a title, something that whenever the technicians are looking at the list of tickets there's something at least identifiable about the ticket. So let's call it title, let's also give that a length of 255, and then we need the description. This needs to be text, so that we have as much information as possible, and we could call it description, but let's call it content because that's a little bit shorter. Now, earlier I mentioned that there is going to be a unique ID, and yes, we have an integer ID, however, when it comes to something like this, I want something that's more than just a number, because numbers are easily hackable. If somebody knows the email address, they could just type in an email address and then start filling in numbers, and then they could find those tickets. So instead of what we were going to do is use an md5 hash, and we'll just call this slug because it will also be part of the URL as well. So slang will be fine there, and we also need to keep track of a ticket status. Now there are many different approaches to tracking the status of something, a ticket, or whatever needs a status. I've seen implementations where it's been a string where there's maybe one character or two. I've even seen it where the status is the entire string. So, it would be pending, or completed, or in progress, or things like that. A "correct approach" would be to define all of our statuses inside of another table, and then have a foreign key in our ticket's table to refer to whatever status, and in a real-world application, that's definitely what we would want to do. And then we would want to take it a step further so that the people managing the tickets could define their own statuses. Something that would be meaningful to them. But in this case, once again we're going to keep things simple and we're just going to use a simple numeric value. So, [LAUGH] if the value is one then it's going to be pending. If it's not one then it's simply going to be completed. Now this is at least going to get us started, because I am using an integer value. It's a tinyInteger value, because really, in this case, we don't really need all of the values available with a full blown integer. But this would get us started at least for referring to an ID in another table, but we aren't going to do that. So we have our integer there for the status, and then finally we need the user ID. Now this is not who submitted the ticket, instead this is going to be the technician that the ticket is assigned to. So, this is going to be an integer, because it needs to link to an ID in our users table, so we will call it user_id, although if we wanted to be absolutely specific we could say technician_id, but user_id is going to be fine. Let's also make this nullable, because whenever a ticket is submitted, well there's not going to be anybody assigned to it yet. So it's going to be null by default, and that is going to get us started. So let's hop on over and let's run this migration. Now in my case we're going to see an error, and it is this, max key length is 767 bytes. Now the reason why we are seeing this here is because I have an older version of MySQL, and it's easily fixable. So if you get that error, don't worry. If you don't get the error, don't worry. It's easily fixable. We just need to go to app > Providers > AppServiceProvider.php, and we need to add a used statement to the scheme of facades. So after Support\Facads\Schema, and then inside of the Boot method we will say Schema :: defaultStringLength(191), and that will fix that, however, we do have something in the database. So let's go ahead and clear that out. Let's refresh our tables. We have migrations and users, and we will just Drop 2 Tables so that we can run the migration, and everything will work just fine, except, no, it won't. Of course it won't, because I cannot type. Okay, so finally, we'll run it again. We have our tables and we are good to go. So in the next lesson we are going to create our form so that people can submit tickets. Now we could go ahead and seed our table here, but we will eventually need that ticket submission form, so we'll just create it in the next lesson and submit our first tickets, so that we will have data to work with.