So welcome to lab seven and this lab seven is a one hour video a one hour exclusive exclusive as your table video right. So, already in lap four I just talked about the as your storage and in that I just defined that what is as your tables, but I did not really go in depth right. So, this is like a one hour exclusive exclusive as your table. In this I will talk about what is as your tables you know how they are schema lists, you know and how they have a structure which is of a key value pair and so on. So, I will go and define as your tables, I will talk about partitions, I will talk about row keys, how to use C sharp to connect to as your tables. We will talk about point queries hot partitions, duplicate records, aggregate data, compound keys EGT transactions and then also we'll talk about a lot of as your table design patterns you know which you can follow like interact, interact table design pattern, inter table design pattern, delete pattern, long tail pattern, you know and so on.

Right. So this is an exclusive one hour As your video has your table video, and I know that one hour video is very tiring, right? So what you should do is you should watch like 1010 minutes. So relax, watch for 1015 minutes, pause the video go out, relax, watch for 10 minutes right? And watch the video because I feel that this video once you watch this video, you would almost know everything about as your tables. So, let us start now before i deep dive into as your tables, let me do like this.

Let me first go and demonstrate you know how as your table look like looks like and then I will go and put down a definition around as your table. So what I will do is I will create an as your table I will add some rules to it and then we will come out with a definition right. So for creating as your table you need to go and create an as your storage. And anytime you are confused, you know from where to go and click where from the menus right. Just click on this create resource and put a search here for example, I'll just say table and Then in the table, I can, you can see that I'm getting a storage account table cue right? So I'll just click on this.

And I'll say create. Remember, we covered all this in lab four, we talked about what is this account type, we talked about what is a classic deployment model? What is the resource, we talked about the different drives, we have standard and premium right. So, I will not go in depth into talking of these things, you know, because already I have discussed all this in lab for so your I will just give name here, shift storage, right. I'll just keep this as v1, standard. Remember, we talked about LRS LRS, all those things we talked about in the first as your storage video.

So here, I will just take LRS because I don't want to get charged too much. Let us give a resource name here. So I'll say resource one and resource 123 You must be then and I'll see create. So, the first thing is we need to go and create an as your storage account and then in that as your storage account we can go and add tables we can go and add queues and so on. So, you can see that there our shifts storage has been created. So, let us click on the storage and let us go ahead and create a table.

So you can see we can create files we can create a blob, right but we are interested in creating a table service table service that is tables. Now let us go and add a table. So we'll add a table name here called as hospital management system. Now wait a minute. Now you must be wondering that this is a table. So in case if you are designing a tables for hospital management system, then the table should be like patient table It should be doctors table medication table.

So, why is this name hospital management? Why are we giving a database name here right Why am I giving a database name here. Now, please note that in case if your mind is comparing the name of table with the table of SQL server or Oracle or IBM as then you are mistaking over here if you want to map the database and tables concept you know with as your tables then you need to think in such a way at the top level is tables as your tables. So, for example, over here at this moment if you see I have created this hospital management system, so, this hospital management system is actually a database. And in this you know, we will have tables which are actually created partitions. So, if you think about it, the database is nothing but the table and your RDBMS tables is nothing but the past So you can see now I have created a table, let me go and add some records.

Once I add some records, you understand what I'm speaking here, right. So this is the tables created here. So now in order to add records, what I will do is I will use the as your cloud Explorer. Remember, if you install the as your development kit, you have this as your cloud Explorer. So I'm going to go and refresh here. So you can see out here, there is a shift storage.

Inside the safe storage, we have tables. And in the tables, we have hospital management system, right. So now let us say you want to go and add a patient record. So I'm gonna go and open here to add a patient record. Now please note, I'm using this cloud explorer because we do not have a good user interface here to add records to those your tables. So that's why I'm using this cloud explorer out here.

So first thing you can see over here is that there are three already reserved fields you know one is a partition key, another one is a row key and the other one is a timestamp, right? So let me go and add a record here. Let us say I want to go and add a patient record. And in the patient record I want to add patient name, I want to add the patient medication, I want to add the patient's age simple, right? So I'm going to go and click on Add out here add an entity. The first thing you will see that when I go to add an entity it says that the data will be stored in a name value pair.

This is very important to note here point number one to note the data is getting stored in a name value pair. So this is not really a column thing, right? So now first thing partition partition actually divides your data into different categories inside a table. So for example, in this table, I want to store patient records right or in and I would like to also store doctor records, I would like to also store billing information. So the patient is categorized by some kind of a party. Addition which I can say, patient partition, right, let's say patient, okay, inside that partition, every row will be identified by the row key.

So I'll say okay p 1001. So there is a patient which is p 1001. It is lying in the patient partition. Now, this partition is what you can compare with the RDBMS tables. Okay? I will add a property here that I also need a name of the patient, right?

Patient name. I will say shave, I will also need the patient age. So I'll say patient age. Let's see 40. I will also see say that I want the medication given to this patient. So let's say he is given tablets, right.

And you can also see there are data types basic data types like integer Boolean double, right, I've taken at this moment, everything is a string. So I'll go and store this. So you can see now in the table App Store, one record called as the patient record and it is lying in the patient partition right. Now let us say I want to store one more record here called as the doctor's record. So let's say I want to put the doctor name and I want to put the doctor address right. So I can go and again add here.

So now this is a doctor partition. Now look at this. This is a doctor partition. And this is let's say d 1001. I'll add a property remember everything has a name value pair, okay, everything is a name value pair. Doctor name is let's say test.

And I will add that let's say doctor fees, the doctor fees is that we put it as number here and teacher 64 bit you know, something hundred, okay. I'll say okay. Now look at this. Inside one table as your table that is the hospital management system. I have two partitions. One is a doctor partition which actually stores the doctor chords.

And the other one is a patient petition which stores the patient records, right. In other words, as your tables are schema lists, there is no schema as such, there are partitions, and there are name value pairs. So this is very different as compared to the RDBMS tables in which you're thinking about. So your RDBMS database is nothing but the as your tables, your RDBMS tables is nothing but the as your partitions as your table partitions. So in case if you're mapping our DBMS with as your tables, which is actually unfair, but I'm sure that with that word table coming in again, and again, you'd like to map it, then then please map it in this way. The RDBMS database is the as your tables and your RDBMS tables is the as your table partition.

Okay, I can again go and add one more record. Let's say this time again, I want to add it to the patient partition patient petition. So I'll say I want to add record p 1002. Now remember that if you want to keep the consistency, right, then please note that you have to keep the same property. So you can see here, I'm putting the patient partition, then I need to put the property exactly as it is the patient age, the patient name, the patient name, and medication, I can see I have made a mistake the patient age, then I need to please note that I need to also make a mistake in the new record as well. Right.

So I'll put here the patient name so this time the new patient name, I'll add one more property. Remember my patient age is the spell mistake. So I'm going to make the same spell mistake here. 30. Right, and I will add a medication. And medication is let's say, that was tablets This time I'll give him setups right setups.

So now in the Patient partition, we have two records and in the doctor partition we have one record. So if I put before you a holistic view of how the as your table architecture is, at the top we have the storage account. So when I created that shift storage that is a storage account, inside that I can have a table for example, I created the hospital management system. In that table, I can have partition so I can have partition one like patient or I can have partition two like doctors, right. And every partition has a partition key and they have a row key to identify the row, the number of fields of one partition can differ from the other partition. Okay, so that's why we say that as your table schema lists.

Now, one more point to remember is that a storage often as your storage maximum can be 500 TV, one table in as your table can be maximum 500, TB, TB and if you are, if you want to perform transactions, right if you want to perform transactions, Write, then transactions can only be performed within a partition means for example, I can only perform transactions within this partition, but I cannot go across other partitions, right. So just wanted to make sure that you know the architecture of how as your tables will look like at the top we have storage, then we have tables we can have we can have number of tables, and then we have partitions and row key and then and each one of the partition records are different in structure but they lie in the same table. So, now that we have seen a demo of as your tables we have added some records we saw that how the structure looks like it is time to put a definition around as your tables.

So, as I've said, when I started this video, I said that first I will demo you and then I will put a definition on as your tables. So, as your tables can be defined by the three s. The first thing is as your tables are schema less. So one partition record row can be very much different in the from the other partition in the number of fields, even in the same partition. You can have different number of fields right. So, it is schema less second is the values are stored using key value pair. So, if you remember whenever I was adding a record in the table, it was also always saying Tell me what is the key what is the value and as your tables do not take SQL did not follow our DBMS concepts, right?

You need to actually call the API so it is no SQL it is SQL s. So, as your tables are nothing but they are a way to store data in a structured key value format, which is schema less and follows no SQL concept. So, that's what exactly is as your tables these three s pretty much sums up what your tables are meant for or what as your tables are actually. So now that we have created the table on the as your storage, let us see that how can we bring this data inside programming languages like C sharp or Java right. So let us do at least with C sharp language here. So for C sharp, the first thing you need to do is you need to go and inspect All the Windows as your dot storage nougat package, right? Because this windows zero dot storage has all the client library to access blobs, files, tables and queues, right?

So go ahead to manage nougat. So right click and say manage nougat and ensure that you have the Windows as your dot storage installed in your program. If if the Windows as your storage is properly installed, you can you should see something like this in your reference as you can see here, this is the references here, right. Great. Now, the next thing is, so now let us say we want to get this patient data into our program, we want to read the collection of the patient data into a program, right. So in the C sharp program to receive this patient data, we need to have some kind of an object structure, which is very much similar, like the patient partition data, right.

In other words, I need to first go and create a class here. So I'm going to go and create a class here so that this class object will receive the patient data right? So I'll create a class here called as patient. Now, these classes need to inherit from the table entity, this table entity is a class you know, which belongs to the Windows as your storage Windows as your storage table, right? And in this in this in this table entity, if you see, right, if you see this class, this class already has two, three ready made properties, you can see partition key, row key and timestamp, right. So these properties you don't need to create manually they are already ready made available in the table entity.

Okay, so at this moment for the partition key for the row key and for the timestamp, I already have the properties right. Now the next next properties which I need is I need the patient name, right. So I'm going to go and create a property here. That is the patient name So prop tab, tab tab, patient name. Also I need to go and create the other properties like patient age and medication. So, you can see that I have created the other two properties as well please note that the patient age is wrong, right the spelling is wrong.

So, because I kept the spelling wrong, I have just in the Properties also I have kept it wrong at this moment, or else the mapping will not happen. Now, once you have created the class right now, the next thing is, we need to go and first connect to the storage account because Please note, at the top, we have the storage account. So remember the structure. So if you remember the structure at the top, we have the shift storage, the storage account. Then we have the table and inside the table, we have our tables, right? So the first thing is I need to go and create an object of the cloud storage account so you can see I'm creating an object of clouds to account.

And you can see that there is some kind of connection string here, which specifies that how you can go and connect to that table. Right. So the next thing is I need to go and somehow get access of the access of, I need to get the unique connection string of the shift storage, right? So you can see here, down below, you know, there is a connection string. So you can see that there is key one, that is key too. So I'll just take the first one here.

I'll copy it, okay. And I will paste it here. We're just going to paste it here. Okay, so the first step out here is that create a connection with the storage account, right? And we give the connection string here and we say cloud storage account dot parse, and it actually gives a reference you know, to the It gives a reference to the first it gives a reference to the storage account online. So basically that cloud storage account, this cloud storage account object here, this storage account object here gives me a reference of shifts storage, right.

The next step is that we will go and we will say that in from this storage account, you know, I need to get access to the table client object. So, this is a second step. So, this is step one, you have created a connection to the storage account. And step two, you will access the table client, the cloud table plant, right. And then inside this table client, you will say that, I want to get access of the table hospital management system, right. So, then you will query Step three, you will say get reference to the hospital management system.

Now, remember, because we can have lots of tables inside right at this moment. You can see that We just have one, but I can have in this cloud explorer I can have hospital management system I can have er p system, right so many other things, isn't it, but at this moment, I just want to get reference of the hospital management system. So now that we have access to the table object, but from this table object, we just want to get the partition data of patient or must be the partition data of Doctor we do not want to fetch all the records right? So for that, we need to go and create a query so we can see the step four is we will go and we'll create a query aware we will only fetch patient partition right. So to create a query we will say table query and we will create we will try to get one with the patient data.

So you can you can look at the way I am creating the object. So I am saying table query. I want to only get patient data and then you can say okay, maybe I can first simplify it rather than putting an ad out here. So Let me just simplify this code out here. Remove all this. Let me simplify for you.

So you can see I'm seeing our table query patient where table query the filter condition is that partition key is equal to patient so I'll say your partition key partition key partition pa RT idea that's right is equal to patient right. So, you can see our for the table query the way we write the queries in the where clause, we use this constant out here table query right, which has a generate filter condition in the Generate filter condition, you have to put the key name you have to put the value and then is it equal is it greater than so you can see everything is out here. Okay. Now this query, I will go and I will run it on my table one which I have created the top right, and I will look through the patient records and over here would like to display the patient name, right?

And must be I would like to display the patient medication, right? Patient medication. Also, like I would like to do a console dot read here so that, you know, I can see the records, right. So if you really see there are four, five steps, actually, first one is step one, you create a connection and you get connected to the storage account. Second is the second, the second and third steps are one step in where you actually try to get access to the table object, right? So you try to get the access to the hospital management system or whatever.

And then from there, you can then go and create a query and fire it. For example, you can query you can create a query on the partition. If you wish, you can create a query in one of the other fields as well right. And then the last step is, you will go and you will start looping. Step five, fire the query and loop To the collection, right? Great.

So we are almost done here, let us see this works or not. So I'm going to go and run this in a debug mode. So I'll put a debug point here, right on this connection string. And remember that this connection string, you should always put in an app config file or in a web config file, you shouldn't be hard coding like this. So let me go and run and let us see that this works or not. So there, the application is running.

So first thing is it is creating a connection with the storage account. Then it gets the reference of the table hospital management system so that it is you can see the reference. After that we create the query. And then this query is executed on the table. That's the final step, right? So once the table once the query gets executed, you can see that he's displaying shift that's the first patient name.

After that he is displaying roger that is a second patient name right. So if you see that is the output, right. So In order to go and get data, we need to first create a reference to the storage account, then get the reference of the table on the table we fire the query and then we get the collection you can take this collection, you can bind to a grid, you can display an HTML table and so on. Also, let us see how to do insert update delete operations. So, in order to do insert operation or a delete operation, we need to do the following the first thing is we need to first prepare things okay. Prepare means for example, let us say if you want to do an insert, then you would create a new object, you would fill it with some data right?

After that you know this prepared objects right. So first is prepare the object on which you want to do the operation like insert, update or delete, then this prepared object you will attach it to operation. So, for example, if you want to do an insert operation, then you would attach to an insert operation and finally, you will send this insert this operation whatever it is insert operation or delete operation to the table. So, for example now let's say I want to go and add a new record right. So, to add a new record, first thing I will go and I will create an object of patient. So let us say this is a new patient new pad.

Let us fill this with some data. So new pad dot patient name is equal to let's say new pad one. Let us also we need to give the partition key so the partition key is patient right Remember, this is a patient record. So it will go in the patient partition. We will also go and fill in the patient age something. So, so you can see over here I'm filling the record, right?

And then this record, I would like to go and do an insert, right? All right. So there it is, I filled a patient record. After this, I need to attach to an operation. So what kind of operation I want to do, I want to go and do an insert operation. So table operation.

So now over here, we want to do an insert operation. I can have a quick look at my notes here. So table operations, so I'll create an object of operation. So I'll say this is an insert operation. Right? And I'll say, table operation dot insert.

So you want to go and insert this entity which is the new patient right? So there it is. And then this operation, we need to go and send it to the table object, which we have got the reference. So if you remember, we have the step three where we have the table operation. So I can go and say table one dot execute, execute the operation, which is the insert operation. So, in general, you know, when you say you want to do an insert when you say you want to do an update or a delete first is you prepare the object.

So, for example, because this is a new record, I have created a fresh object. If this was an update, I would have got it from the collection. After that you create the operation and you do an insert, update whatever is operation and then send that operation for execution right. So, let us go and run this. So, I'm going to go and click on this let us run it. So, there it is executing So it creates this new patient object and then inserts it so you can see it I'm saying table one dot execute something is bad out here that is do a view detail send a bad request Why?

Because why has it sent a bad request okay. Properties need value. So, there are some values which we did not provide. So, which values Did we miss? Oh, we did not provide the row key. Remember row key is important.

So good. That is good. Right. So Rokia will say this is b one 007 remember row key and partition key are compulsory. So good. Remember that every time you get an error, that is good because with error we learn.

So there it is I'm doing an insert operation. And this time I hope it's successful. So there it is, it is successful. So if I go now to my hospital management system, and if I do a refresh, you can see that that's the new patient, what has been added great. In the CMB, we can also do the update part. So what I will do is let me go and comment the part of insert first.

So there is a part of insert. So again, for the update part, the first thing is you need to go and prepare the object or get the object to be updated. And then Attach this object with the operation and then execute that operation operation using the table, right. So in order to get the object, while it can really do is you can go ahead and first create a table operation. So you can see here, I'm creating a table operation. And what I will do is I'll try to get the record which we just added, be 1007.

So you can see that I'm creating a retrieve operation. And I'm telling you the table that please retrieve the results. And then get me the result. Right. And then I can go and see here, you can see that the new record which I added was new pet one, so I can go and update that entity so I can see an update entity. Patient name, too.

Patient name to something else right. So something some other name okay. And and then after that I can I can go and I can update. So basically, I can go and say. So there it is. Remember the steps you attach this object with the operation.

So one is that first you get the object, right, you change something. Second is you go ahead and you say now that you want to do a replace operation, and you can see that I've attached the entity with it. And then I can go and I can see table one dot execute this update operation so, now So, you can see it now the name is new pet one and this should change. So you won't run this. So, remember the process is always same first you get the object, right you prepare the object and then afterwards you go and you attach to the operation, which is update, which is insert, and then you go and you update the operation on the table. So, if you see now, just the updation has happened you can see now the value is new Pat here, but if I refresh, you can see this something else right.

So that means updation has been successful. So that was the insert part. That was the update part. Now, I would like to also go and run you through how you can do the Delete operations or delete operation again remains the same. You have already retrieve the object, you need to just go ahead and create a delete operation. So this I will not demonstrate.

We have already demonstrated it So I'll just put it here in the code so that you can see it later on. So, if you want to delete same thing, you will retrieve the entity and you will say delete and then say execute. Also there is one more thing here the batch process in case you want to go and add batches right. So, what you can do is you can create a batch operation, you can keep on adding data, and then you can say execute batch. So, again, this one, this code, I will paste, you know, in case you want to see later on. Okay, so this is again a batch operation.

So, you can see that the API of as your tables are quite powerful, you know, you can do batch operations, you can do update, you can do delete, you can do insert, and so on. So, that brings us to the end of 30 minutes of demo. So, in this 30 minutes of demo we looked into as your tables, we look into partitions, row keys, we saw how to write a C sharp program and So on. But what I found is that more than understanding as your tables, you know, one of the things I found is that when you start dealing with huge amount of data, you need to be aware of some of the best practices or else you will end up with a very, very bad as your table design. Okay, so here are the 10 important best practices you need to remember when you talk about your tables.

The first point here is think about search first, remember that as your table, if you look at the nature of as your table, it is a denormalized format, it is a key value format, it is a flat, kind of a structure, right? So as your table was created, mainly to make your search faster. So first, think about that. What are your inputs to your program, and how you can make it faster. So for that, the first point to remember is that always fire point queries. Point queries means whenever you find any query, please provide the partition key and the row key if you do not provide the partition key, and if you just provide the row key Then it will do as your table scan.

So for example, so let me just switch to our code. So if you remember our code over here, for example, over here, we just provided the partition key, right? But we should be providing both the partition key and the row key. So in other words, you know, this query could have been tailored something like this, I can say table query dot combine filters, right? So the first filter is that it's a partition key. And then the second filter is that there is a row key.

So always fire point query. So you can see I'm creating an combined key over here. So I will say that table operators dot and so this one and this one. So here I will say row key and in the row key, I will say p 1001. Whatever it is, right. So remember that always fire point queries.

Any query which is coming to as your table please note that you need to have the partition Key and the row key, if you just provide the row key, then it will actually do as your table scan. So, that is the first point to remember that think about search first and always whatever query you are firing to as your system as your table system, it should be a point query. The second point is data storage is cheap. So do not worry about duplicate data Do not worry about redundant data, because many of the developers come from the RDBMS background you know, so they learn normalization they learn first normal form, they learn second normal form, their gurus, you know, teach them that see that normalization is most important, redundant data is so bad and so on. So somewhere in that mindset, you know, they have this thing saying that let's not have duplicate data, but today if you look at things around right, you know, the hard disk USB, they are so much cheaper that we do not really have to worry about duplicate data.

So in case of as your table also it says that create duplicate data with multiple keys. aggregate data, right means what? First thing, if you look at as your tables costing of, of data size you can see here, like, you know, for one terabyte per month, it is point 07 gb. So per GB, so that's like, you know, around like 60 $70 per month, which is very cheap, right? So do not worry about duplicate data. In other words, if you look at the as your table structure, the row key is that is the clustered index.

The row key is by which he actually goes and narrows on to a row, right? So the row key and the partition key are like those indexes, you know, what we have in our DBMS? So let us say, I'm adding a data out here of an employee, right? So he has an employee table, right? And in this employee table, I'm having e 1001. And let us say that, so this is the employee code you can think about right?

Then this employee has a name right? So shave the employer also has an email address, right? So here is an email address, okay share@yahoo.com whatever. And also the employer has salary so I will have a salary here as well, right? So let's say 1000 or something. Now you can see here that we have this employee partition, in that we have the employee code iE 1001.

So using this I can go and I can search very quickly the record of shift but now let's say somebody says, I want to also go and search by email address we can see this email over here right? But this email is not a rookie, it is not the partition key right? and searching by email could maker as your table scan. So what you do in this kind of situation is you will go and you will add a duplicate record. So must be I will go and add an entity here. And I will say that this is m EMP search by email.

Right? And you're the partition key would be the email address. So I'll say This is my partition key. Now look at this, we are having duplicate records. But because the row key is like a clustered index, it's like an index right? We need to now make two records right which are duplicate.

So at one place, I can go and use the row key where we have the email address and there is another situation where I will use the row key which is the employee code, right. So other things would be all Same here. So this is the EMP code. And EMP code is e 1001. The name is Shiv and the salary is 100. Now you can see here, I have added a duplicate record.

Oh look at this. This is very important. I've added a duplicate record here of employ. But by using one of the row key I can go and search by using EMP code and the other partition I can go and search by using it email address. So, this point over here says that create duplicate data with multiple keys. So, in case you have two three search criterias right think about it and see that you can if you want you can go and create multiple keys with duplicate data.

Also like you know do not shy away from creating aggregate data kind of partition for example, now, let us say that at some moment of time, you want to find the count of the employee you want to find the salary of the employee and so on, right. So, what you can do is you can create one more partition here called as EMP stats, right? And the rookie here can be, so let's say that this is in our stats, okay, stats is a rookie. So one of the stats I would like to add here is how much is the count of employees. So at this moment, I have one other I would like to add is that how much is the average salary right average salary? So at this moment, I have only one record, so thousands Now we can see that this is very interesting.

There is one EMP stats over here, you know, wherein I have stored the average and the count. So every time I add an employee record, I'll be adding one to the email address search, and one, I'll be updating the stats. So the aggregate data I will update. So tomorrow if I want to go and say that okay, what is a count of employees, I will just go and fetch this partition and this row key, and I get the value, right. So the second point is very important, that create duplicate data with multiple keys on which you want the search. And also, if possible, please create aggregate data so that you do not have to do the calculations again and again.

So when you do the insert, do the pre calculation and storage and then go ahead and just fetch the aggregate data and display it. Now the third point here is compound keys. One of the other design which you can think about here is you can see that over here I have created two partitions. But if you want you can do something like this as well for example, rather than creating one more partition like this, right what we can do is we can do as follows. So, we can add, let us say, employee partition, and we can create a compound key a compound key means for example, I will say okay the EMP code is 1001 and the email address is shift at the rate yahoo.com. So, look at this slash Shiv at the rate gmail.com.

Now, you can see the row key at this moment is a combination of two keys right. So I can go and search by using both of them if I wish. So again, this is one more option which you can which you can exploit. So other things will all remain same, right? But you can now go ahead and Search or your rookie now is a compound key What is it out of range okay must be this is where things start becoming interesting okay that is so that the slash was not allowed, but you can see now, here is a row key which is a combination key of employee code and an email address. So, I can use both of them and I can search.

So, again in case you are having some searches, you know where you have compound key, you can think about this fourth point is avoid hot partitions. So, what are hot partitions? Hot partitions are nothing but you know when one of the partition is getting overloaded with lot of queries, for example, now what happens is when you create a partition, one partition can be max of a size 500 db, and it will run on one of the nodes on one of the virtual machines right. Now, think about it. Let's say you have a partition of an employee and in that you are so By using employee code and also somebody or some people are searching by employee email address right. Now, assume for now that it has one Li EMP code as a rookie.

So, now what is happening is all the searches are going to that partition. Now, if the partition gets overloaded what as your does is it actually goes and splits a partition into two virtual machines. So, it says that okay looks like this partition is getting overloaded. So, let me split it up and put into two different virtual machines right and that is bad. So, what you should now do is you should create two partitions one partition, which is actually be searching by using EMP code and the other partition where you will search by using EMP email address the one which we did recently. So with that, what will happen is one partition will not become hot or I will say one partition will not become overloaded because now you are split the search queries into two different partitions and every partition will lie in their own virtual machine.

So again, this is important whenever you are designing your searches, ensure that if the searches are in heavy in size, if people are doing a lot of searches of that type, probably it is time to create a duplicate record, create multiple partitions with multiple Roki. The fifth important point is avoid creating unnecessary as your tables, right? Because you won't be able to do transactions or I will say eg t entity group transactions. Now, now what are transactions so transactions means you know, normally the normal transaction definition is that either all of the records get committed, or either all of the records gets rolled back, right. So for example, now let's say you are adding to patient records here, assume you know, and when you're adding to patient records out in one of the table, you would like to say that if both of the either both of the patient records should get saved, or none of them should get saved.

Okay? Now this is only possible within one table So, transactions are applicable only within one table in case your rows are getting split into some other table, then transactions become difficult. So, remember all the logical units you know all the logical entities which are connected with each other for example, you can have patient and then you can have patient address, you can have patient billing. So, all of this probably you want to insert into one transaction, you want to update them in one transaction, you want to delete them in one transaction right then you should put them into one table, they can be separated by partitions that is not a problem, but they should be in one table in case they go across tables, then the transactions are difficult. So remember, do not try to go across tables until it is very much necessary. If you are going across tables you know that means that the entities have one table is not connected with other tables directly.

But if they logically belong to each other, if there are relationship between each other, then you should be putting all the entities separated by partitions into One table. So, we have covered like five important tips. Now, you can see that from six to nine points, there are actually some patterns. So, these are actually database design patterns. So as your table design patterns, you know, which you can remember when you're designing as your table. So, you can see there is interpretation, there is interpretation, there is a delete pattern, there is a large entity patterns and so on.

On the Microsoft site, you know, this is discussed in length, but like, you know, I would like to just give you a glimpse of what these patterns are, right? If you're following the first four points, or first five points, right, you're already implementing these patterns properly. Okay. But you know, by knowing these patterns explicitly, it will make things more clear, right. So I'm going to go and let us start with intra partition patterns, so in intra partition patterns, so, let me open up an Excel sheet out here and let us discuss. So now let us say you have an as your table called as employ, and in that we have something called as department partition, right.

So now what you can Do is and let us say that in this department you have employees so you want to search by using employee ID or you want to also search by using employee email. So, what you can do is you can create one kind of a key where you can say okay, this is EMP 1001 right. So, EMP 1001 this is EMP 1002. So, there can be field one like we can have name of the employ we can have designation of the employee right. So, this is Sheriff this is you know supervisor This is Roger is something else right. Now, let us say I want to also search by email address.

So what we can do is I can also do like this, I will have the same partition department, right. And I will say EMP email, over here is Shiv at date yahoo.com Look at this. This is interesting, because now, your key here is differentiated by EMP or EMP email right So again this data is same this data will be same, right? Remember duplicate data is not a problem. And again I can have over here, department as a partition, I can say this is Roger. Now you can see here that in the same partition department, right, I can differentiate I can search by using emp id.

So to search by using emp id I have to concatenate EMP hyphen and then the ID and if I want to search by using EMP email, I have to say EMP email, hyphen, whatever is the email so I can search by both of them and everything is done in one partition, okay. So this is termed as inter partition. So basically if you see here, so, intra partition, so intra partition means within the same partition, you have differentiated the records by using the By using the key by the row key, right, the second is you can go into a partition in intra partition, it's the same table Remember, you should always try to keep the same table Do not try to create multiple tables. When you create one as your table, think in your mind that you have created a database. I again repeat this sentence if you have created an as your table, it is not exactly the table of our DBMS it's actually a database.

So you should try to create more partition because so that you can have transactions you know you do not overload you know, your table and so on. Right. So basically remember that creating one more as your table is creating one more RDBMS kind of database right. so here also you can see I have I'm going to keep the same partition here. So here I will say now, this is department wherein I am going to search by employee ID Can you see or like this is EMP and here I will give the employ key 1002 This is a second partition which is by email. And here I will have the email address.

Remember this is the key the row key guys, this one is the row key. Now, look at this design this is interesting in this actually you have created two partitions. So, this is one partition and this is another partition. So, you have actually created two partitions in this everything is in one partition, and it is differentiated by the row key. So, this is in this is within the partition and this is outside the partition. So, you can see here in the Microsoft if you see, they have given such kind of a defined definition.

So, interoperation means, within the same table within the same part And interpretation means you're creating more partition. So this one down below is inter partition, and the top one is inter partition. So this is one pattern you can remember intra partition and intra partition. Now, the next kind of database design pattern as your pattern as your pattern is the Delete partition pattern, what is this delete partition pattern? Okay, let me explain you. So I'm going to go and copy this.

Right. And let me put it here. Let's say that you have an audit trail table. So you have created an audit trail table. Yoda is an audit trail table. Okay, this audit trail table.

What it does is it stores audit, you know. So for example, think about that the user logs in user logs off he does some kind. So, basically, I have a row key and I will say that what action had been done and who has done it, right, something like this. So now what I can do is I can see one partition out here that this is an action that say action Delete. Oh god I'll just say action for now, or I will see cool operation Come on let's let's uh cool operations on partition. So in this I'll say Rocky, I'll say okay, I'll put the date.

So I can have a date here sing Okay, this is one 2008 the action was an insert action who did it shift it right? In the same way. On the same day, you can also put the time there wasn't delete done. Right? In the same day, there was an update done and oh by this is delete was done by Roger right. I think about for a single day.

You can have lots of records which are inserted right. Now at some moment of time, you know, you'd like to go and tear down a tree. How do you go and delete it because you have stored it gateway. So you have to actually go and loop through every day and then do a delete and so on right. So rather than doing this you know, you can do something like this you can create one partition saying this is crude of January. Look at this.

This is crude oil. January right and must be over here. You can have elegance let's say this is your crude of January and this can be some ID you want to store the storage here like 112 thousand eight fine This is crude of Jan again you know this is this right then you can have a crude of fab Okay, this is one more partition now remember this is one more partition, remember you should not worry about the size you should not worry about you know how much will be the size because size is cheaper Remember I talked about $70 per month for one TV so the cheaper so don't worry about it right. Now let's say you want to go and delete everything for for Jan right. So you can just go and say partition is Jan and delete all of them. So this will just go and delete in one go and then it goes off.

Right. So if you are looking at bulk delete so it like an audit trail. We don't do a search a lot. We actually just take out an audit we have a look sometimes in one month or two months or in a year right? But afterwards when the audit is done we want to do a major delete right. So, it makes a lot of sense to have partition wise entries rather than having row wise entries.

So this is Tom has a delete partition. So remember, when you have bulk delete, and let's search on a on records, you know those kind of records you can apply the Delete partition pattern. Also, very quickly, I would like to discuss about three other patterns. One is the data series pattern. A lot of times No, you'd like to store the data like our like, you know, for the first hour hour what happened for the second hour what happened and so on. Right?

So rather than storing it like a row key like this, you know, what you can do is you can store it as a field. Like we can see it it's the same thing, rookie but I'm storing as a field. You know, in this case, you can quickly go to employee ID and then just look up you know, like the the first hour or second hour because remember the hours are fixed, right? It is just one to 12 or one to 24 Okay, so you can use this In case you have to if you want to store images and documents side what you can do is large entity patterns you can follow store the record and the photo or the document you can store it in a blob, you can store it in a file storage, right. So, again this is one more pattern large entity pattern, there is one more pattern here, which is the long table pattern over here a lot of times you know, we want to go and store for a particular for a particular record we want to store a lot of fields and let us say those fields are about 255.

Now if it is about 255 then in one partition or in one row you can maximum have some 255 fields right so what you can do is you can create one row key like employee ID equals 01 and then store from one to 252 then you can have another row key and you can say store from 253 to 365 right. So in case you have a lot of fields, then you can follow this pattern. So these are The 10 the nine practices which are discussed and we are one final one optimistic locking, I will talk about it. So, intra partition pattern right. intra partition means, within the same row key I will identify two different records, intra partition interpretation, I split across partition delete pattern, I stole more at an aggregate level like January February, then we talked about large entity patterns we talked about a data series pattern and in case you have a lot of fields right.

Now, there is one more thing called as the optimistic locking which you need to remember. If you remember, when we were storing records, right, when our records were getting stored, we had something called as the timestamp. Now, this timestamp is used to do optimistic locking right. So, in other words, when you talk about blocking this time stamp is used, so the timestamp so this is how it works. Let's say you you create one record, let's say with some rookie 1001 value shift. And then we say, Okay, this is updated at 10:10am.

Okay, on, let's say 112 thousand 18. It is updated at 10am. Right? Now, two users pull the record out. So user one pulls it out, and he starts making changes, right? So there's user one who's making a change, then you have user two who starts making changes.

So there's user two who also starts making changes right? Now, let's say user one first goes and changes the shift to Roger. Right. And then he updates this as 11am seeing the last updated is 11am. Now if user two goes to update, right, then he will say okay, the previous time when I fetch the record, it was 10am. And now it is 11am.

That means somebody else has changed the record. So it will throw up an exception, it will throw up an optimistic locking exception. So that is by default implemented in as your tables so that's why we have The third column which is, you know, a result column, the timestamp, right. So in case you know, when you talk about optimistic locking, when we talk about concurrency, it follows optimistic locking. So these are the 10 important practices in which you can remember when you're talking about as your tables. Great.

So that brings us to the end of this video and I would like to congratulate everyone because we have completed five hours of as your training right? And still there is more to come in. So keep watching and thank you very much. And the next video will be on blobs or queues I need to decide what is the what should I take but blobs or either queues is the next video which I will be recording. Thank you