Planning the Database3:04 with Ben Deitch
In this video we'll talk about Data Definition Language (DDL) and make a plan for structuring our database!
[MUSIC] 0:00 Hi, I'm Ben and in this course, we're going to talk about how to create and 0:09 manage tables in a database. 0:13 We've already seen how to query tables and how to add new rows. 0:16 Now we're gonna take a step back and 0:20 see how to create those tables in the first place. 0:22 To create tables in SQL, we'll need to used data definition language, or 0:25 DDL for short. 0:29 As its name implies, 0:31 data definition language is what's used to define the structure of our database. 0:32 If you need to create or delete a table, you'll use data definition language. 0:37 To help us learn about data definition language let's work through an example. 0:42 Let's say we're in a band and we've just had our big break, a stadium tour. 0:47 We've picked the dates, booked the stadiums, and rented a tour bus. 0:52 So now it's time to start selling tickets. 0:56 Since we're a pretty big deal, 1:00 the only place we're going to sell tickets is on our website, 1:01 which means we'll be responsible for keeping track of all the ticket sales. 1:05 Luckily, we know a bit about databases so this shouldn't be too hard. 1:10 But before we dive into creating tables, let's start by taking a minute to 1:15 think about how we should structure our database. 1:19 First, we'll need a Tickets Table to store details about 1:21 each of the tickets we've sold. 1:25 Then, inside that table we'll need a unique ID for 1:26 each ticket, followed by information about which concert the ticket is for. 1:30 We'll need the date, city, state, and venue for each concert. 1:35 After that we need to know who the ticket is for. 1:40 So let's add FIRST_NAME and LAST_NAME columns and 1:44 also an email address in case we need to send any updates about the concert. 1:47 That looks pretty good. 1:52 Let's see what it would look like once we've inserted some data. 1:53 There's nothing inherently wrong with this. 1:57 We could do everything with one table, and probably make it work. 1:59 But down the line, this approach ends up being a lot more complicated. 2:04 For example, if we wanted to add information about a new concert but 2:08 weren't selling tickets yet, we'd have no way of doing it. 2:12 To fix this we should pull out all of the concert data into its own table. 2:16 Let's put the concert data in a new Concerts Table. 2:21 And in the Tickets Table, let's replace it with a Concert ID column. 2:24 We can also have repeated data about who's buying the tickets. 2:29 Maybe they're going to multiple concerts or just buying tickets for a group. 2:33 Either way, we can save some more space by making a table for 2:37 our ticket holders as well. 2:41 This process of removing repeated data is called normalization and 2:43 it's one of the first things you should be looking for when creating a new database. 2:48 If you've got a group of columns that keeps repeating, 2:52 then it's usually time to create a new table. 2:55 Now that we've decided on the structure of our database, 2:58 in the next video we'll start creating it. 3:01
You need to sign up for Treehouse in order to download course files.Sign up