Database Design 17 - Parent Tables and Child Tables — Transcript

Full transcript

1. 0:00hey guys what's up uh before we start
2. 0:02the many to many relationship design I
3. 0:05wanted to talk about parent child
4. 0:07relationships so we have uh when we
5. 0:10design these relationships a table is
6. 0:12always the parent and a table is always
7. 0:16the
8. 0:17child and uh when we're talking about
9. 0:19keys keys keep things unique and they
10. 0:23always they're they're used to connect
11. 0:25tables that are related so every T table
12. 0:28has a key that keeps keeps it unique
13. 0:31when we create a foreign key it
14. 0:34references that primary key in another
15. 0:39table so the primary key is the
16. 0:45parent the foreign key is the
17. 0:51child the child points back to the
18. 0:55parent it inherits the values from the
19. 0:59parent for example uh the parent's ID is
20. 1:0586 then the foreign key pointing back to
21. 1:07the parent is 86 it's the child so in a
22. 1:12one in a one toone relationship where we
23. 1:15just store everything in one table which
24. 1:17is the most practical way to do things
25. 1:19we don't have to worry about primary
26. 1:20keys or foreign Keys we just have one
27. 1:23table and that's all we have then we
28. 1:25have columns within this
29. 1:27table so we have the user ID the
30. 1:30password the username the the whatever
31. 1:33else all those are just columns within a
32. 1:36table we don't have to worry about a
33. 1:38parent or a child because we're not
34. 1:40making any relationships across multiple
35. 1:44tables but when we get to one to many
36. 1:47that's when we have this table
37. 1:51pointing or has uh many children so you
38. 1:55would say this is the one
39. 1:58side it's the
40. 2:01parent then we have each of the parents
41. 2:08children these all point back to the
42. 2:12parent with a foreign
43. 2:17key so if you think of like comments on
44. 2:20a YouTube video every comment has a
45. 2:24poster or a a user account of a person
46. 2:29who clicked post a comment that means
47. 2:33that the commenter is the parent and the
48. 2:38comment is the child another way to
49. 2:41think about
50. 2:43that is if you if you were given a
51. 2:47comment on YouTube you can figure out
52. 2:51who posted that comment that's because
53. 2:53the child always points back to the
54. 2:55parent now if you look at a user account
55. 2:59on YouTube such as Caleb the video maker
56. 3:022 awesome Channel check it out and be
57. 3:04sure to subscribe well you don't
58. 3:06necessarily know every single comment
59. 3:11that that parent has given there might
60. 3:12be a feed in there that says recent
61. 3:15comments
62. 3:16but when we look at the child we always
63. 3:19know the parent because it inherits the
64. 3:21values but the parent doesn't inherit
65. 3:25anything from the child think of like in
66. 3:28real life you can see a married couple
67. 3:33and you don't know if they have children
68. 3:35or not but if you see a little baby will
69. 3:39you think all right they that that baby
70. 3:42has a parent or
71. 3:46parents that's the similar idea with
72. 3:49database design think of this wall here
73. 3:52between the parent and the
74. 3:56children the children inherit values
75. 4:00from the parent such as the forign key
76. 4:0387 that tells us who the parent
77. 4:07is that is important I know it seems
78. 4:11like okay who really cares who's the
79. 4:12parent or child just get on with the
80. 4:14database design you Noob I'm going to
81. 4:17unsubscribe well this is important
82. 4:19because when we get to understanding how
83. 4:22to design each relationship we need to
84. 4:25understand where we put the foreign key
85. 4:28which table does the foreign key go
86. 4:30into that is decided by the child
87. 4:35because the child always has the forign
88. 4:37key that points back to the primary key
89. 4:40so user ID primary key user ID foreign
90. 4:45key points back to the primary key user
91. 4:49ID so foreign Keys always go in the
92. 4:51children primary keys go in the parent
93. 4:55when it comes to many to many
94. 4:57relationships we have problems with
95. 5:00parent child relationships and that is
96. 5:03why we can't store many to many
97. 5:05relationships in a database and that's
98. 5:07what we're going to talk about in the
99. 5:09next video when we talk about many to
100. 5:11many relationships
101. 5:14specifically but just know that when we
102. 5:17have an entity think I want you to think
103. 5:21of whether or not it would have a parent
104. 5:23a lot of the times it would but other
105. 5:25times not so much but if you think of
106. 5:29think of an order for a store an order
107. 5:34needs a parent because an order is when
108. 5:39you buy
109. 5:41product who is buying it someone has to
110. 5:44buy it otherwise it wouldn't it wouldn't
111. 5:47exist the child would not exist without
112. 5:51the order so let's let's re look at this
113. 5:53this is the the user and this is the
114. 5:57orders we can't have a child child
115. 5:59without a parent logically it just
116. 6:01doesn't make sense so if there is no
117. 6:04parent of the order the order cannot
118. 6:07exist we need to keep that connection an
119. 6:11order needs someone who bought that
120. 6:16order hopefully that's kind of making
121. 6:19sense so yeah just keep in mind parent
122. 6:22child relationships I'm not trying to
123. 6:24repeat myself but I'm trying to repeat
124. 6:26myself so you get this drill in your
125. 6:27brain foreign key goes on the the child
126. 6:30primary key goes on the parent and soon
127. 6:33we'll be talking more in depth about
128. 6:34keys so just for now memorize primary
129. 6:37key and foreign key primary is the user
130. 6:41ID the foreign key is a reference to
131. 6:45that user ID it's in a foreign table and
132. 6:48it points back to the original table so
133. 6:50yeah that's all I have to say thank you
134. 6:52for listening to me ramble and I will
135. 6:54see you in the next video peace and
136. 6:57subscribe