 So now that I've built out each one of these tables and I've imported my data into invoice patient and Visit the one thing I want you to notice is again say patient ID. This refers to the patient table and then Invoice invoice refers to my visit ID. Well, what happens if right now I come in and I make a New invoice for a new visit. Well for a visit Even more so that doesn't exist You know, I'm embezzling money and oh, hey, you know $5,000 Bezzling scandalous So how what do I have to do? Well, I clearly don't want this kind of thing to happen I don't want to be able to input those kinds of records into my database So what I want to do is I actually want to design out a relationship. I want to say that I Can only make reference to visit IDs that belong in my visit table and again I only want to make Visit IDs that have patient IDs that belong in the patient table again That's why if we look at While you go to the doctor you have to put in that new patient information before they'll let you be seen It's because they have to then record your visit and then they have to bill you for that visit So how do I do that? So what I need to do to design out my relationship is I come over here to my database tools section and again I keep using that word relationship It's almost as if it there's a button there that does that and there is now if you don't get a nice little Dialogue box to pop up right away. That's fine. It's not the end of the world because you can click on show table and Show table will bring every single one of your tables available to you Now again, I said that if we thought about it every invoice has to be associated to a visit and every visit has to be Associated to a patient any one patient can have an infinite number of visits Any one visit could have an infinite number of invoices so to speak so I'm going to go ahead and click on invoice I'm going to click add Then I'm going to click on visit because again any one invoice can or any invoice has to have a visit ID and Then I'm going to come over here because any visit has to have a patient ID and I'm going to add them Now once I've done all three of them. I'm going to go ahead and just hit close The last thing I like to do it's more for just kind of my style of sick choice I like to make sure that I see all my data. So I do like to resize them just a little bit Now how do I make a relationship happen to do that all I need to do is Pick the field that I want to kind of establish as my relationship click hold Drag over to the other patient ID and as soon as I let go Notice what pops up a dialogue box saying that the patient ID in the patient table is going to associate to the patient ID In the visit table now that just says that there's a relationship going on that these things talk to each other But I have to force that these changes mean something You know don't put a visit in if a patient ID Doesn't belong inside of my patient table. That's actually where this enforced referential integrity comes into play I'm saying don't put a patient ID in the visit table if it doesn't exist in the patient table What it's basically saying don't record a visit unless the patient has been recorded first The other little bit I'm going to say is to make sure we update cascade related fields Now the reason behind this is because what we're saying here is if for whatever reason the patient's ID number changes I want it to reflect to everywhere where I have a reference to that ID number that way You know, I don't lose any medical records now Here is one of the things that we do not worry about cascade delete related Records well that would say if I say for example deleted a patient I would delete every record also from my visit. This isn't so good because well We can think about that in a lot of different sense. What happens if our business here was audited by the IRS Well, I have to probably produce all of these invoices and in theory if I delete a patient that has an associated visit Well, guess what that probably means that these visits will if I did this as well in a second I might remove the invoices as well and I don't want that because then I get in trouble with the law So I don't want that so I do want to just maintain Enforced referential integrity cascade updates again. I said it's a one-to-many relationship Any one patient can go to our urgent care any number of times. I've done it You know, it's not like it's not the opposite way around. So as soon as I hit create You see exactly that I get any one patient can go to an infinite number of visits And I'll just move you over here and you down here and notice it'll link to it because then I said that any one visit Can have any number of invoices. I got it. I'm charging you for the tongue depressors for the Lawson's I don't know. I'm not a doctor. I don't know where they charge But I go to patient. I I go the same thing. I go visit ID click hold drag to visit ID. Let go I Have to enforce my referential integrity. I have to cascade the updates, but again, I don't select cascades elite because What if I get audited? I don't want that. As soon as I hit create again any one visit can have an infinite number of of invoices Associated to it and there now I've made my relationship if I now attempt to close out of that Are you sure you want to say that? Yes if I attempt to now make a visit if I attempt to make a new visit ID That associates to a Patient ID that does not exist. I know 9 9 9 9 9 does not exist and we'll go ahead and sneak this in Oh, this happened yesterday because we're embezzling and I hit enter You cannot add or change a record because a related record is required in table. I have to Make a patient before this is going to work Otherwise, no So it is a pretty nice little kind of those checks and balances access is so Headstrong on making sure everything is very precise because now that I've built my foundation. I Don't have to worry about it anymore