Okay today we talk about MySQL workbench this is
a client to connect mySQL database where you will be able to run queries if you want to create a
connection new connection you just click on this and provide the credential and connect I have
already created one connection but right now we need to talk about how to create a model or ERD
diagram so I clicked on this I'm clicking on new so here we can add a new diagram
here let me click on this I will change it to blog post Dash or maybe
under
score here I will select utf-8 mb4 and I would specifically go for uh Unicode utf-8
Unicode underscore CI so this is this should be my default collation where I want to save
so I want to save it into a specific location so blog post is our project it can change an
install folder and then it contains a DB folder or you can call it model so I keep this model
in my project so that if somebody has to change something they can directly change in this file
okay and please remember this applies on
the MySQL because this is uh my SQL workbench it it
uh help you create schema with that and we can benefit from it in so many things by generating
the queries Etc so I just I want to create a table naming convention so usually we just prefix
our table so blog post with posts so this is a simple site where you can read the blog you
can read different posts so we are trying to create a table for it so I prefixed it with the
blog post and then I'm writing the table name here we'll create blog
underscore ID Auto increment unsigned Auto increment its
value will be automatically assigned and it will increase by one unsigned means uh so any
data type which really refer to a number has a positive and negative number so we are just saying
that we just want to use unsigned mean whereas where it's signed at the positive number or a
negative number so it split the range into two um so it split the overall capacity into two
ranges one is positive one is negative so we only want to consum
e the complete storage uh
as a positive so that's why we are saying it should be an unsigned data type then we will
have title and we would use VAR chart 256 why um because I want to apply an index a text based
index so that it can quickly search it so for that we need at maximum support 256 character
and then we will have description here where we can have 500 Words so this is anything you
want to write and then we can use uh keywords which we really print on the page for
the Bots to craw
l our page and they can read your site based on these keywords so I
will use 500 and then the body uh if it is a small body so we'll go with the text which
is a lot more bigger than this number but again this has a limit I will discuss
about this once we bridge this limit um so I I'm gonna have a flag which
is published it should be enum type published yes no or rejected okay and if we want to have the
default value for this so by default nothing would be published here
so we are marking i
t not published okay so that's it uh the things that I need I will
delete this extra column so this is our table here we have created all the fields
we'll save let's close it and now I'm going to create a categories table
again I will edit this prefix with my database and why we prefix there are so many reasons
sometime you get the hosting where you are allowed only to create one database so if your tables are
prefixed separately it's really easy to create all the tables in one database and
then use them the
second thing that you can use why you can use it to to make it difficult for people to guess your
um table's name but if he knows your Technique it's really easy for them to understand but you
can use any three random number for your database here I will use the categories table I am going
to create a category ID and again this is big end Auto increment and unsigned so here we look at
this website which is myskilldap.matchcal.com uh you can see here the integer is of
4 b
ytes whereas big end is of 8 bytes and the number integer can store is from minus
this number to maximum this number so it is assigned integer that's why it has a range in the
negative value and range for the positive value the maximum value uh that we can save if it is
unsigned so it's a huge number if we use the big end which is 8 byte if we use unsigned so it's
really really big number so let's say this tool of this tool or website or you are creating for
everyone maybe like medium where
um different people come in and create their own website an
article so this number should be really really big um for now big end is enough for us okay so
we will have a category name here and then we will have 255 characters okay let's save
this and close this I think that's enough now I want to create the relation I always
create relation by going here and name it like FK so it's a foreign key so this is key
name and the key name is very important we are create trying to create this rela
tion on post
and the other table would be category or you can just call it yeah so on the posts table I am
building the relation for the category and here the reference table would be categories uh okay
we didn't created that column so let's go there and create here the column it should be category
underscore ID and it should be big integer uh the same as on the other side if you do not
use the same data type or unsigned value then it won't let you create the relation okay
now if I click h
ere so we can see here the category ID so we are building this relation
so if these both column does not match due to the data type due to the signed unsigned
flag it won't let you create that relation okay at least not this tool so now the referential
Integrity so how it we can enforce it on update we want to update the child record so if the ID
is change in the parent table it also it must change in the child table as well so parent table
here is the category table the child table here is
the post table and on delete so if somebody
delete a category it will automatically delete all the post in it or we want to restrict so
this is the safer option we don't want it to um do this because this is going to create a
problem for us so it should restrict and throw an error which we will see once we start
writing code for it uh from that error we will be able to understand there are some child
records so we first need to delete them or move those posts to a different category okay l
et's
save this and let's create another table here I'm going to name it uh it's
a blog post uh app version so title if you want to mention any
title uh we can use 256 character so I'm using all small case all small case all
with underscore so here we will have to save the um number and for default we can
use now so whenever this execute it will enter the current date current
times time so this is a Unix epoch okay and that's it I want version id1 title and
updated Etc let's go here for th
e insert so I want to create insert here I'll click on this
so we are going to version ID new installation and yeah let's not provide this let's apply this
and come back here okay so we have created a table to track the version so that we can upgrade our
database Whenever there is a change so let's go to the forward database forward engineering
let's export this thing so this panel will ask you to provide the database connection details so
I have already created a local connection so local
connection localhost simple pretty straightforward
there is a next button here I will click on it it asked me to skip creation of foreign
key generate index so there are a couple of options I don't want to use any of them I just
want to use omit schema qualifier and object name why I'm using this because I do not
want it to write the database name every time it creates a resource in
my database so before the table name I wanted to use generate use statement so that it
select the proper dat
abase uh okay let's go next so here I want to export MySQL table and objects
if you have other things you can select select options accordingly but usually we just select
this option now we click on this and it has generated some code for us so let's look at this
code so here it is trying to save unique check previous value so whatever the value of the unique
check is it saves all the configuration first and then override all the configurations so here it is
saving the current value of uniq
ue check into an old variable and then set it to zero so it's
basically disabling unique jackets disabling foreign key and couple of other things it's just
disabling all of them but before disabling them it is also trying to save them as well so here
if we come down we can see here so create schema if it does not exist so it would be blog post so
default character set is the is the something that I wanted so our character set is utf-8 and before
and collation is utf-8 and before underscore
Unicode CI why we chose this I will explain it in
later videos now here we want to use the blog post or this will select the current database that is
just created after that is trying to create the table if it does not exist so it will create
it this is our big and unsigned not null Auto increment field category ID 256 corrector supposed
to be but I just missed and then this is an extra column we need to remove this it's really easy
we go back here I did this and we'll have 256. yes okay an
d we need to delete this extra
column uh yeah save this again close it and then forward engineering all the option
will remain as it is next next and here we can see now that extra field is fixed another
table we are trying to create is blog post uh which is the title description all those
field um and then we have a primary key then we are creating an index so on primary key index
is automatically created and then we are applying a constraint it's a foreign key constraint uh
post has a co
nstraint over the category or the category ID would be used here as a foreign key so
this is the field here in the current table and it will match against category ID of the categories
table on delete we want to restrict this we don't want to allow it on update we want to update
this engine is nodb for this table as well and similarly for the uh app version
we just created a new table okay so um so here we we have table but I think there's
something missing so let's go back and try to okay
here we can see the option and generate insert
statements for the table so so I'm I just clicked on this and when I go to the new screen now I can
see here app version with everything yeah and new installation so here is that missing something
is missing here will fix that let's go here and in the inserts we can say now let's apply this okay and again do the forward
engendering so once you have learned this it's really easy uh yeah now we can see the statements
so what is happening it is cu
rrently so first it save all the uh all the current configuration
then it override all the configuration to speed up the process that's how migration scripts are
written usually so this is not something special with mySQL workbench this is how things
are done you first disable all the checks um you create your table you make all the
changes then restore all the Restriction all the configuration all the checks so
here we are just restoring it because we saved them previously and then we star
t working
on our work which is basically insertion of the record uh and then we just committed so let's
save this I will save it into the same path in blog post install DB so here we will say
first install or just call it install dot SQL so whenever somebody has to create the database
he or she needs to run this script on the database and just press cancel and we are back here so
that's how we were able to create our database in second part we'll look at how to make
the changes into that t
his database and publish those changes so how can we
do that so stay tuned thank you bye
Comments