Which Database Type Should I Use For My App?

welcome to this episode of Dev questions with Tim Corey join us as we tackle the questions you are asking about a career in software development understanding the industry and new technology if you are just starting out or you want to grow stronger as a developer this is the place to get your questions answered now here's your host expert developer and online educator Tim Corey which database type should I use for my application this is a question I was asked recently on a suggestion site I thought to answer in today's episode of Dev questions now let's get the answer out of the way right away and then we'll talk through okay so the answer is it depends okay so that's the answer to pretty much any question you ask but let's talk about the criteria think through what's the best database type you should use for your application so the first question I want to ask is what's the purpose of a database all right so what is the purpose of any database you have for any application well it's there to store your data right it's there to hopefully give your data back to you quickly all right and then it's there to secure your data and it's there to protect your data all right so secure and protect um think of this way secure is like no one could access your database who shouldn't and the safety or security of your I'm sorry safety or protection of your database is like is it backed up so if something happens to corrupt it is it protected against that so is it you know your database is designed to protect your data as well as secure it is there to store your data as there to quickly give you back your data those are the kind of four big buckets that we're talking about when it comes to a database all right so here's an example for you um the I am Tim Corey database site or I'm sorry the I am Tim Corey site has a database I guess we store data that is used to generate and populate the site and that's kind of a database but that database is a Json file it's not SQL or no SQL or any of those others the reason why is because it allows the storage of all data you can store data in a Json file it's extremely fast to access it's as secure as it needs to be I mean I don't care if you read my Json file go for it read it all it doesn't matter because that's all public information and then it's protected by Source control so that if it ever got corrupted I just republished the latest build and we're good to go so when you think about your databases don't say oh well it must be SQL it must be you know some big expensive system some complicated system a database could be as simple as a text file now do I recommend text file storage for your database needs not usually I think that usually you want to kind of grow beyond that and use systems that better secure your data because usually you do care if people read your data and maybe you want to have a better backup system than just it's in Source control okay and the reason why my Json works is because you don't write to it when you go to my website you don't create anything that ends up getting put in that Json file it's a read-only database the only time we change that data is when we physically go in there and make a change to file and then commit that to Source control so that's why it works for this situation but not for every situation in fact not for most situations but when you're thinking about databases don't limit yourself don't start with okay a database must mean some variant of SQL SQL MySQL SQL Lite You Know download list no no stop back up and again think about the goals of your data how is a data database going to be used what is it needed for and so on so this is where we need to talk about the size of a database if you've got a large project and by large I mean you're expecting well over millions of Records Millions in that case you have less options okay there's some things that just won't work for instance uh sqlite that it will not work for millions of Records it just it shouldn't work it can you don't want to do that okay so you're kind of limited to a you know either SQL a major SQL version or a nosql version okay and and that's just whichever flavor works best for you so we'll come back to that but it really comes down to the skill of a team working on it and the previous database is used and the costs associated so if you're an environment that already has 18 Microsoft SQL databases and you say what database should we choose for this project you're probably going to lean towards Microsoft's SQL database because you already have a lot of the infrastructure you already have a lot of experience you already have a lot of the systems in place to help you build those databases and the costs associated will be pretty low and if you said well actually we're going to try and use Oracle for this one that's probably not a good call because you've got a whole new database system and a whole new set of commands and all this other stuff you don't want to mix Oracle and Microsoft SQL you don't need to um and you probably wouldn't go with a nosql database unless you had a good reason you'd want to stick with what you already have all right so there are times of change but in a big situation usually you end up relying on those criteria now for a small database not by small I mean less than a million records Okay so we're talking about you know databases that really can handle even a medium-sized business because even a medium-sized business uh just doesn't have millions and millions of Records all right usually so for a small database there's lots of options and they're going to appear based upon what your needs are so let's talk through some questions you can ask does it need to be portable meaning if you deploy an executable file to a client machine you install it using MSI or something else and it needs to work offline you probably want a database next to.exe well that's a portable database you don't want to install Microsoft SQL Server you know SQL Express or something like that on their machine and have it all set up that's all complicated for just a portable database so that's question number one question number two is this a single user or a multi-user database meaning if if I create a let's say a to-do list and maybe I didn't put it as a web app but only I have access to it just for me I'm not going to publish it because my goodness there's a bajillion to do apps on the market so I just want for me I want a couple of tweaks to it that's a single user database so that will be a different Choice than a multi-user database you could potentially even still use SQL Lite or goodness Microsoft Access it could work even though you would not want to do that for a multi-user environment all right number three doesn't need to be secure again coming back to my Json example my Json did not need to be secure at all does yours need to be secure and how secure so what level of security you need to have as far as who can have access and what type of access and limitations for instance Microsoft SQL Server you can say your login only has access to these three store procedures that's it all you can do whereas this login over here can access all the store procedures but no tables reviews and yet the store procedures can call those tables reviews whereas this login can access tables views store procedures all of them but it cannot modify any structure of those tables views or store procedures and this one over here can do backups and restores but can't really access the database so there's lots of different things you can do based upon the levels of security you want to set up inside a Microsoft SQL Server you cannot do the same thing with a SQL Lite database okay so there's different levels depending on you know a SQL light database you could have a login and password that protects the database where you get in that's you know that's kind of the levels you can have so which one you choose is going to depend on what levels of security you need to have now number four does this database need to be replicated this is one that until more recently um SQL was not good at okay SQL has had replication for decades but I say Microsoft SQL but it's not been easy and it's not it's been complicated and expensive and all the rest and then no single comes along and no SQL like uh mongodb or Cosmos DB super simple to replicate okay you create database and replicate across the globe and boom you're done so it doesn't need to be replicated and how much replication do you need to have uh read write replicas or do you want to have one database that's the read write and a bunch of read-only replicas that changes things too so that's the fourth question to ask number five how fast does it need to be now when you're first turning off everybody that is especially newer but it's it's really it's a disease we all have and that is I want to be as fast as Google I want to be as big as Google I want to have it have as much throughput as Google you're not Google you're just not so realistically how fast does it need to be if it needs me Lightning Fast okay that changes what things you can choose so here's some examples okay so if you have a a single user portable database need okay one user needs a portable database SQL Lite that's a good option now you can also look at a CSV file that you don't have to have protection and there's other options but sqlite probably a good option okay if you need a quick cheap database that's replicated across the globe mongodb maybe Cosmos DB those are very easy to replicate and they're very easy to start very very small and very very cheap all right if you need a small multi-user database that maybe you're already working with some SQL databases or have some knowledge of Microsoft SQL Server Azure SQL great place to start uh five dollars a month I believe it is for the tiniest Dev database that is super small but still works as a full SQL Server five bucks a month okay maybe it's ten I think it's five um anyways super small version um for a small multi-user database if you need extremely fast database that's used for caching redis okay you want to look at redis because that's what they're designed for is for caching it's still a database but as designed for very very quick lookups because that's what's most important is that super fast speed all right so different databases for different situations go to that checklist of of those questions and say what kind of fits in best now here's my recommendation and in order to use that list of questions that I gave you you need to follow this recommendation and that is get to know the various database types because if you don't know what's out there then how can you make a decision on one versus the other and then even if you know what's out there you need to know their strengths and weaknesses because every database is different in strengths and weaknesses all right if you don't know those how do you compare the two there is a bajillion flavors of SQL like I said MySQL knows our SQL Lite Microsoft SQL Oracle these are all SQL type databases where use a SQL command structure and said relational database and all the rest and so why would you choose one or the other you have to know his strengths and weaknesses now put a big emphasis when you're learning these database Types on understanding both the SQL structure and the nosql structure too often people say you know SQL relation relational database is the only way to go really because all your caching databases are all nosql databases so why are you saying that you don't want to Cache your data in a Microsoft SQL Server that's just not the place to do it you want to Cache it in a a redis structure because that's a nosql structure and in fact there's not just one nosql type there's multiple okay you have a graph database and you have you know the key value dictionary type pairing and you like there's so many different types of nosql databases you should know them all and how to use each one effectively put emphasis on knowing both the SQL structure and the nosql structure don't throw out tools too often I hear people say well you know this is the best tool so therefore throw that one away don't do that put the tools in your toolbox know when they're used most effectively because doing that will allow you to make the best choice for your situation at the very beginning I gave you the answer to this question and the answer was it depends and if you say this tool is best throws others out then you're saying no Tim you're wrong it doesn't depend this is the tool and that's just not correct there's a reason we have multiple different types because different database types fit different situations better all right so once you pick your database once you say I've I've narrowed down based upon my knowledge based upon the pros and cons and the strengths and weaknesses and the costs and all the rest I beside this is the database of choice great now really get to know it okay really dive deep into that database don't just stop at the surface level too often I see people say well you know here's my pet peeve I use Anthony Frameworks so I don't need to know about SQL has just a bad idea because you need to know how to get the most out of your database how to use it well how to secure your data properly how to properly back up your database so that it's protected you're protecting that data how to restore that database properly how to you know manage who has access to it and make sure that it's locked down as much as possible how to get the most performance out of it possible I've seen people complain about databases being slow when they're using really bad queries well write a better query but in order to do that you have to know how to write a better query so go deep into understanding your database that you're using don't just stop the surface level and say I put data in I get data out we're good okay know it better than that all right so that's my answer to the question which database should I choose it depends but there's the criteria for choosing and evaluating which database works best for you and a quick example here I have a C sharp Master course where I take you from knowing nothing to being a c-sharp developer who can get a job as at least a junior level developer if not higher well in there I have a complete section on databases and we don't just choose one database type we go through SQL Microsoft SQL SQL Lite nose or mice I'm sorry MySQL we go through using both Dapper and Entity Framework yeah I know we also use nosql like mongodb and Cosmos DB we also look at link we also look at a lot of different things to make sure you understand how to access data properly how to be the best at accessing that data so you understand I have 18 different tools I have 18 different sizes of flathead screwdriver why because different screws have different thicknesses and I need the right screwdriver for the job okay or I need to have the right whatever other tool for this job so I go I spend a lot of time in there because it's important to not just say SQL Server it's it's the one Microsoft SQL Server it works great with c-sharp and it does so therefore that's the solution to every problem it's not okay make the right choice that works for you okay hope I answered your question thanks for listening and as always I am Tim Corey [Music]

Channel: IAmTimCorey

Views: 30,702

Keywords: c#, c# developer questions, dev questions, dev questions series, developer questions, how do i, iamtimcorey, learn c#, tim corey, database, the right database, how to choose a database, best database, what database to use, best database for website

Id: 7nBVXutEgIA

Length: 18min 15sec (1095 seconds)

Published: Thu Nov 24 2022