Primary & Foreign Keys

hopefully the sunny databases the question is what does this mean and how do you do it let's try and break it down into bits okay tables what a tables are that sorry pause there are two kinds of databases right simple will complicated one what do they got that file and relational now when you hear tables which one we're talking about a relational has to be relational right within a relational database each table is a little flat file on its own okay and we used to represent this we still represent this where they are a schematic diagram for schema do you remember that okay so okay and then you know each table describes a particular kind of entity and then you have the different attributes or characteristics of each one so the idea is that you can lead tables using these somehow okay so how would we do this let me think of an example say um we run a business and we sell stuff okay so for instance I am yeah question when you got a book on you wrote primer in foreign capitals oh oh that's exactly confusing okay should we start again sorry now suppose I've been keeping my records for my business and I start off small so all I just uses a spreadsheet start off with a flat file okay so for instance I just um as people come to me I'm like will they make orders and so I'm going to put together a table which possibly know which orders are going where and gave me the history and make sure I've got the right amount of body pain the right amount taxes and so on so for instance I might have say in whatever bar over here okay and I say well I'll just do it in sequence so it's give me a 1 2 3 4 5 ok and then not only do I want an order perhaps I want to say ok I want to know who bought this from ok so I'm going to customer I'd be an online business ok so obviously I've got to know where I'm going to send this stuff to so I'll have an address yeah ok and suppose there's a whole bunch of other fields that tell you about the actual products that get ordered ok and so this again it's actually several attributes over there we're not going to be interested in I want to focus on this part over here I suppose as my business starts off I'm just getting different people all at random okay so I get freaky on to something and Steve order something and orders something and you know I know what their addresses they live at number one Kalen foot road college route oh and I've got other dresses here and so on ok and it's so fine so far right but the problem video when I start to have the same people coming actually and giving me repeat business over ever again because for instance suppose Ricky decides the price that I really good so he comes back you know if you orders another fee it's like ah forgot about things so I'm going to put it another order okay and you know Steve other people and I'm going to have the same people ordering again and again again now can someone tell me what's the problem in this scenario yeah the huge remember redundant docking okay good so every time Ricky's in here his address is going to be in here as well right so one kind of had wrote elephant I'm coming from calendar every single time okay and suppose you know I want to look up okay where where do I send things to Ricky okay suppose I want to send him a flyer or you know some promotion or something like that right I have to go back and look through where his addresses but what if he changed address right and you know some of these are different to others and I look at the wrong one and send it to the wrong place okay so redundant data not only is it a waste of space okay but also can lead to these kinds of problems so how do I solve the problem well instead of having one table which is says all my orders in it okay I'm going to break apart a flat file into two tables and make it a relational database right so first I have orders okay and then I have part of this which relates which is customers okay so here's what I'll do orders I'll still have an order ID right that'll be one two three four five six seven okay the next thing I would have is information about the customer now I don't need to put their name and their address every single time okay because that's redundant so instead I might have say a customer ID customer five customer 23 okay now even there I'm already saving space because just having that number is shorter than having a full name okay and then you know there will be some stuff about the products and so on okay now the customer ID I look at the order online customer 23 I don't know who that is so in order to actually have some description here I've got to go over to this table answer each customer like we said they have an ID they also have a name they also have an address just for the sake of simplicity let's just treat it like that okay so let's have a look at this in the customer table okay what's the primary key customer ID what is a primary key after all it's a field right that uniquely identifies every different record in this table okay so I'm going to put my key icon okay next to customer ID that's the primary key really so maybe you want to write PK now it leaks to this table see like so okay so here in this table customer ID is not the primary key because it doesn't uniquely identify every quarter but it's still a king it's the foreign key it's forward because this isn't the table it really originates from the originated over there so you can see having a primary key over here that's a foreign key or here that's what joins these two creates a relationship between them okay now just for the sake of it since we're at it what kind of a relationship is it you've got three choices it can be one-to-one it can be one savetti or it can be many to many what do you think one-to-many who thinks is what to me you know a few people how can you prove or justify explain why it's what's a bit like one customer for copy one customer can write more than one okay so each customer can have many orders all right so there's a committee so it's obviously not one for one okay what about orders but the one order it's one customer I can do okay go ahead so each order is only going to go is going to be sent to one person right so that's why it's not many to many people right so now which we said you put one and which one sided we put many on because my blonde customers attorney on one goes on customers and many goes on orders okay you can think about it this way one customer has many orders right but orders only have one customer you see what that's that's how I read it okay so that's how you on that's how tables get leads through primary informative so I want to ask any questions to clarify things okay thumbs up

Channel: Eddie Woo

Views: 290,461

Rating: 4.9330239 out of 5

Keywords:

Id: B5r8CcTUs5Y

Length: 8min 25sec (505 seconds)

Published: Thu Jun 06 2013