1 00:00:00,490 --> 00:00:03,530 So far we've learned about the three types of relationships. 2 00:00:03,530 --> 00:00:06,790 One to many, many to many, and one to one. 3 00:00:06,790 --> 00:00:11,490 Now we'll see how these relationships are depicted during the design process. 4 00:00:11,490 --> 00:00:14,580 When a database architect is designing a database, 5 00:00:14,580 --> 00:00:18,650 they use special software to model relationships between tables. 6 00:00:18,650 --> 00:00:21,920 There are entire books on this topic of database modeling. 7 00:00:21,920 --> 00:00:23,240 So we'll just keep it simple. 8 00:00:24,340 --> 00:00:29,360 During the design process, the model speaks about tables as entities. 9 00:00:29,360 --> 00:00:33,730 Entities eventually become tables in the physical database. 10 00:00:33,730 --> 00:00:38,200 But during the design, we speak about them take conceptual level. 11 00:00:38,200 --> 00:00:43,390 They models that database designers create are called entity relationship diagrams. 12 00:00:43,390 --> 00:00:46,840 Because they're intended to model the way database tables will 13 00:00:46,840 --> 00:00:51,120 eventually relate to each other in the finished database. 14 00:00:51,120 --> 00:00:55,480 Let's take a closer look at how database design is depict relationships 15 00:00:55,480 --> 00:00:57,560 in their modeling tools. 16 00:00:57,560 --> 00:01:02,380 Here we see two entities, product category and product. 17 00:01:02,380 --> 00:01:05,320 One product category can contain many products. 18 00:01:05,320 --> 00:01:08,880 But a product can only belong to one part of category. 19 00:01:08,880 --> 00:01:12,112 Therefore this is a one to many relationship. 20 00:01:12,112 --> 00:01:14,970 Database designers use a special notation to 21 00:01:14,970 --> 00:01:17,570 indicate the relationship between tables. 22 00:01:17,570 --> 00:01:21,870 One of the most common notation styles is called crow's foot notation. 23 00:01:21,870 --> 00:01:26,760 To show the one to many relationship in this notation style, we make a little 24 00:01:26,760 --> 00:01:31,030 symbol that looks like a bird's foot on the side where the foreign key will go. 25 00:01:31,030 --> 00:01:33,440 This is the many side of the relationship. 26 00:01:33,440 --> 00:01:36,190 This is why it's called crow's foot notation. 27 00:01:36,190 --> 00:01:39,100 Then on the primary key side of the relationship, 28 00:01:39,100 --> 00:01:42,970 we make a little line indicating the one side. 29 00:01:42,970 --> 00:01:45,698 This is what a one to one relationship looks like, 30 00:01:45,698 --> 00:01:49,880 with small line indicators on both sides. 31 00:01:49,880 --> 00:01:53,070 This is what a many to many relationship looks like, 32 00:01:53,070 --> 00:01:55,890 with crow's feet indicates on both sides. 33 00:01:55,890 --> 00:01:59,860 Of course, as we learned in the video on many to many relationships, 34 00:01:59,860 --> 00:02:02,070 tables can't support this relationship. 35 00:02:02,070 --> 00:02:04,940 So even though it makes sense conceptually, 36 00:02:04,940 --> 00:02:07,130 we have to resolve this in our design. 37 00:02:07,130 --> 00:02:10,310 We do this by introducing a third table 38 00:02:10,310 --> 00:02:13,900 with the foreign keys to the two outside tables. 39 00:02:13,900 --> 00:02:16,980 One must note about entity relationship diagrams. 40 00:02:16,980 --> 00:02:21,432 Crow's feet is not the only style of relationship notation that database 41 00:02:21,432 --> 00:02:22,790 designers use. 42 00:02:22,790 --> 00:02:26,480 Check out the teacher's notes for more information on this style and others.