Placeholder Image

字幕表 動画を再生する

  • creating these classes requires equipment and service.

  • Is that cost money?

  • If you appreciate this education, please think about going to Eli the computer guy dot com and offering a one time or monthly recurring donation.

  • Welcome back.

  • As you know, I am Eli the computer guy, and today we're going to be doing an introduction in it to the Maya sequel structure.

  • When you're going be building out a system using my sequel as the database back in, I want to give you an idea of basically like, logically, what does this look like?

  • So what I'm talking about here is that they were going to be talking about the servers were going to be talking about the database software.

  • So my sequel we're gonna be talking about the databases themselves were going to be talking about the tables we're gonna be talking about.

  • The columns were going to be talking about the records that a types basically giving you an eagle eye view of when we were talking about a database structure.

  • What does this actually look like so that you can then go and try to figure out each individual component and build out what you need So this class today is basically just going to be giving you that eagle eye view of what my sequel structure looks like s O that you have a better idea of what's going on when we start going into the more indepth classes.

  • So for this class today, we're primarily going to be on the white board again.

  • I know I have world class drawing skills.

  • Those drawing skills really help you folks understand how all of these things work and how they all go together.

  • So let's go over the white board so I can start drawing things out to try to explain to you how the my sequel structure looks when you're dealing with it in the real world.

  • So the first thing that we need to go over when we're talking about my sequel structure or really any kind of database structure we first need to talk about what is the front end and what is the back end?

  • So you will hear about the front end for applications and you will hear about the back end for applications.

  • So basically, what we're talking about with the back end for applications is this is going to be your database system.

  • So this is the overall system.

  • So with us, but we're gonna be talking about is, of course, my sequel.

  • But for your back end, you could have Oracle.

  • You could have Maria d.

  • B.

  • You could have some kind of no sequel solution or whatever.

  • Basically, what the back end does is the back and actually stores all of the data so that it can be easily accessed by the front end.

  • Now, what are we talking about?

  • What we're talking about the front end.

  • So from the front end is the actual application your users are going to be using in order to access the data in the data base in the back end that you've created.

  • So this may be some kind of Web applications, So think about something like salesforce dot com.

  • Basically, you go to that Web site, you have a Web page in front of you you're able to put in from in information, you're able to pull out reports.

  • So basically, that's what's called a Web front, and or you might have a mobile front and right, So if you have your little iPhone or you have your smartphone somebody creates a native application for that phone.

  • And again, maybe this is a customer relationship management software.

  • Maybe this is a work order software again, sometime Type of data intensive software basically what'll happen is that mobile app will go back and communicate with your back end in order to be a bit pushy.

  • Data basically store that in the database and then pull it for when you're looking for records or whatever.

  • So when we start talking about things like front end and back ends, this is what we're talking about.

  • The front end, the front end for any of these service's or applications is basically the user interface that people are going to be interacting with.

  • Whether this is a Web interface, whether this is a mobile interface, whether it's the desktop interface, something like that, that is what the front end is.

  • And then the back end is the overall system that contains all of the data that the front end will require to actually give any kind of functionalities.

  • That's what we're talking about.

  • We're talking up front ends and back ends so past this.

  • Now let's talk about kind of the physical structure of the my sequel structure that we're gonna be dealing with here.

  • And so, basically, if you're going to be using my sequel database, generally you're going to actually be spinning up or turning on some kind of physical server on.

  • So, basically, you have the physical server.

  • So it's a physical server from a company like Dell or Lenovo or something that you just build on your own.

  • So again, you get your CPU RAM your hard drive.

  • You build it out however you want, then on this physical machine, then you're going to install an operating system so that operating system is most likely gonna be Lennox.

  • But it could be Windows, or it could even be Mac OS.

  • Or if you're really cool.

  • I guess some people are still running Solaris out there, right?

  • So basically, you're going to install the operating system onto that physical hardware and then on to this.

  • Then we're going to install installed the database software so it is important.

  • Understand?

  • We're talking about my sequel or Maria de B or any of these other database solutions.

  • This is software, just like exchanges.

  • A piece of software, just like SharePoint is a piece of software.

  • You actually have to install this software onto onto a machine, whether it's a physical machine or a virtual machine s.

  • So then you will be installing my sequel onto your physical or virtual machine, and now you have something to work with.

  • So this is now a database server, and so this database server may be part of your back in for whatever solution that you're providing Now.

  • It is important.

  • Understand when you start dealing with databases that you can create things called clusters.

  • So what clusters are clusters are awesome clusters or what you should be using If you have databases eyes.

  • Basically, you can light up multiple different servers, install my sequel onto his multiple different servers and then basically have the databases replicate data amongst all of these different servers so that have won the physical or virtual machine fails.

  • The cluster stays up and running, so basically in this instance, then right if we have a front end here.

  • So if we have a little smart smartphone and that's trying to communicate back with a server, that's one of things you ought to be thinking about when it's communicating with a back end.

  • It may be communicating with just one physical or virtual server, or it might be communicating with a whole cluster of these database servers.

  • And basically, how how the data is routed is determined by what you have coded that type of thing.

  • So the first thing that you need to be thinking about when you're thinking about the my sequel structure is that you actually have to have my sequel running on something, right?

  • If you take a database file and just dump it on a server and it doesn't have the software to actually run it, then nothing is going to happen.

  • So you're actually going to need to install my sequel onto a physical machine and depending on how complicated your infrastructure is, you then may actually create a cluster of these.

  • My sequel databases servers for things like fail over reliability, load balancing and that type of thing.

  • Now, once we get past the physical structure of your database system again with your server with your cluster, whatever you have going off your back end, then we need to actually go into my sequel and see how the databases are actually laid out, right so you have your server, it has my sequel on it.

  • And then within my sequel, you won't have databases.

  • You won't have tables.

  • You're going tohave records.

  • So on and so forth.

  • Now, the important thing to understand with how you design your database within my sequel is it really is up to you.

  • It's It's how you look at things logically.

  • So basically, when you have databases, when you have tables, you are grouping things together a logically now when the problems you run into the room in the real world is what you can serological and what your buddy considers logical may be completely in different things.

  • And so that's one of the issues that you can run into with.

  • Basically, these database structures is that people can designed.

  • The database is really insane ways, because for them, it made a hell of a lot of sense, even though it doesn't make a lot of sense to anybody else.

  • The big thing is, when you're grouping things, what's supposed to be, is it supposed to be a logical So when people go in to do maintenance on the database, when people go and they try to write new code in order to interact with the database.

  • Basically, when they're trying to do the normal, you know, I t related task of the database.

  • It should be rather easy to understand what's going on.

  • Okay, this is the user table.

  • This is the accounts table.

  • This is the parts table.

  • This is the vendor's table.

  • You should be able to go in and very quickly be able to understand what's going on with the database.

  • The problem is, though, that's not a technical thing.

  • You can have software work, you can have a database, works that's ugly as hell.

  • It could be incredibly ugly, and it can be incredibly complicated, and nobody can understand what's going on.

  • But the computer can still make it work because it's still functionally works.

  • So that's one of the issues that you can run into s so the first thing when you're dealing with the my sequel infrastructure is that the highest level?

  • So the highest level we're gonna be dealing with is what is called that database, right?

  • So with n my sequel, So you have my sequel, install it on your server, and then you can create a database and the important thing to understand here is you can create one database or to databases or 20 databases if you want.

  • Basically, again these air.

  • Just logical high level groupings for all the data that you're gonna have now, a lot of times, if you have a company, you will just have one.

  • The single database, right?

  • So you're gonna have one single database that's gonna have your inventory that's gonna have your users that's gonna have her invoicing system and the whole nine yards, right?

  • So that may be what you want to do because one of the cool things with these databases is you can actually have, like, user accounts for the databases.

  • So what permissions people have these particular databases, you can have that type of thing.

  • And so one of the things you may think about, right.

  • So you have a small companies, small company.

  • You have five people in your company and you say, you know, I don't want to make a really complicated database structure, so I will have a single database that will contain all of our tables that will contain all of her data because I feel pretty secure with that.

  • Right.

  • But The question becomes, What happens if you have a larger company?

  • Let's say you have a company of like 1000 people and that company with 1000 people has sales.

  • It also has some kind of kind of like technicians that go out and do things, you know, out in the real world, maybe has some kind of logistics system, you know, inventory, all that kind of thing.

  • And so something you need to be thinking about again from a security standpoint is Do you want everything in one database?

  • Do you want your salespeople and our technicians and your life, your six people and everybody else being able to hammer one The single database or doesn't make more sense to have separate databases so you could have a C R M basically build a C.

  • R M database, and then you could build a work order database and then you could build like an inventory and basically logistics, you know, tracking or whatever database.

  • And then maybe actually, like a sales and invoice database, right?

  • So basically the salespeople, they will go to a one front end, and that front end will point back to the C.

  • R.

  • M database and then the technicians, they're going to go to their front and right.

  • So let's say the salespeople, they're sitting down, they're in the office, they go to a Web interface.

  • And so that Web interface connects back to the C R M database that you've created, right?

  • And so that's all it connects to.

  • And then the technicians, you develop a native app for some for their mobile devices, android or IOS, and the technicians.

  • They point back to the work order database.

  • And so the thing is, the sales people only are able to connect to the C.

  • R M database, and the technicians are on Leah broke connect to the work order database.

  • Then you have the logistics folks on there again.

  • You create some kind of other front and for them, and they're only able to connect to the inventory and logistics database.

  • Why this is important is because then that spreads the load.

  • So the actual resource load to these different databases.

  • So one database isn't getting hammered by every single user in the company.

  • And also, from a security standpoint, right, you know, sales are probably your most vulnerable people, right?

  • If somebody can try toe hack into the sale system.

  • That's probably going to be easiest.

  • So if they hack in the sale system and all they're able to get to is a C.

  • R M database, I mean, it is horrible.

  • It's horrible.

  • But the worst thing that'll happen is your crn data base will go down, right?

  • So if you have a corruption, if something stupid happens, if bad data goes in and starts screwing everything up, the worst case scenario here is then that one database that will get corrupted and then you'll have a nightmare trying to get that back up online.

  • But your technicians will still be able to work.

  • Your logistics will still be able to work, and maybe you know your sales and enforcing that that will still be able to work.

  • And so that's one of things.

  • Be thinking about again.

  • You want everything in one single database.

  • So for security, for corruption, for any kind of problems, you know, load balancing or whatever else you all you want in one database or do you want to spread it out?

  • And the important thing to understand here is Remember when I was talking about those front end's.

  • So we talked about the front end's that.

  • Then communicate back with the databases.

  • Remember, you can have a front end that can actually communicate to multiple databases.

  • So let's say you have some technicians and let's say you have Let's say you have like a lead technician.

  • So you have this system here.

  • And so sales go to C.

  • R.

  • M.

  • And normal technicians go to the work order Well, even though these air separate databases, right, you're creating a front end and a front end can connect to one database or multiple different databases.

  • So let's say you have lied technicians.

  • So let's say the lead technicians are the ones that can go in.

  • They can do work orders.

  • They can do work.

  • But let's say they can also sell to.

  • So you could create a front end for these leap technicians, and the front end could connect to the work ordered out of aces.

  • And it couldn't connect to the C R M database, and it could connect to the sales and invoice database.

  • And so that's an important thing to be thinking about when you think about created.

  • Creating a database is How many of these do you want?

  • Is it logical toe?

  • Have everything in one single database all your data for your company or organization in one single database?

  • Or doesn't make more sense to spread out the information to these different databases again from a security and from a liability standpoint, a reliability standpoint.

  • And then, if you need to be a bit, pull information from the different databases, literally, you could just simply write code.

  • It is easy to write code to connect to to databases as it is to write code to connect to one database may be able to fill out the information whenever application or front end that you have.

  • And so the first thing you need to be thinking about is this thing, this highest level, and that is the database on the database server.

  • So then, once you create your database, then within the database you create tables and so tables, more or less when you visually look at them, they basically look like spreadsheets, right?

  • So if you've ever opened up again PHP my admin or access or something like that, you will seem tables and basically again they more or less look like a spreadsheet on DSO with with tables, you have columns, so columns these air.

  • Basically what data that you're going to be accepting into the database, Right?

  • So let's say this is a user's table, so you're going to create a table for user's right.

  • And so if you're gonna create a table for user's, the first thing that you want is a user I d.

  • So whenever you're going to be creating a table, you want some i D for the records within the table, and this will generally be something called the primary key.

  • So the primary KIIS is again.

  • Remember, in the database world in the computer world, computers can.

  • Computers can't think on their feet.

  • They can't.

  • They can't assume what you mean, right?

  • They can only do whatever they're coded to Dio.

  • And so one of the big problems you could get into a database is is what happens if you have two records that are literally identical, literally identical.

  • So when you go to do a search that's gonna run into problems, because and then you're gonna get two identical records that is gone, it's just gonna become a mess.

  • And so what the primary KIIS is.

  • The primary key is the weight is to make sure every single record is unique within the table.

  • So, basically, generally with a primary key, you do something called auto increments.

  • And and basically, as new records Air created, it counts up.

  • So record one record to record three record four record.

  • Five That type of thing, right?

  • So the first, the first column you're going to have in a table is generally going to be your primary key.

  • It's going to be something such as a user i d a part i d an invoice i d an account i de la la la la In the beginning to make it easiest, you will make that an auto increment thing.

  • So basically, as new records air created, it simply goes up.

  • So this will be a user i d.

  • One.

  • He's your idea to user i d.

  • Three user i d four user i d five.

  • And so what this means is that within the table, even if the other columns have identical information in them, at least this one column in front will be different.

  • Then past that.

  • Then you can have Let's say like the the first name.

  • So if this is a user, you can either first name, so that's that's a field.

  • So if you're gonna be putting that that type of data into your table, then again, maybe it could have something like an email address, if that's what you want, and then maybe you can have something, possibly such as, you know, the person's age.

  • So depending on what you're going to be doing, And so these this is the type of data that you're going to be pulling in to this database.

  • Now, once you have the columns, then you can all.

  • Then you can also say what the data type is.

  • So this is an important thing in the database world.

  • How do you add Bob plus Sue?

  • Riddle me this Riddle me that Bob plus one divided by Twinkie equals what a mess.

  • A garbage.

  • It's absolutely horrible.

  • And so you have what are called data types.

  • And so what data types are is you say what kind of data you want these columns to be able to accept.

  • So if you're asking for a first name or last name or that type of thing, you will then say just simply text.