[MUSIC] We've already seen how rails creates databases using the schema.rb file. The schema file is created as a result of running the scaffold generators and then rake db:migrate. When you're trying to design the appropriate structure for the data in your web application, you'll sometimes find it useful to work at the schema level, so let's talk about that in a little bit more detail now. What exactly is a schema? Well, it's the formal structure and organization of a database, it's how you document that. Now, a database can be instantiated from a schema. In this case, the database itself is what holds the data, the actual records. An the schema is just a design, it doesn't store any records. A very common way of representing a schema is to use an entity relationship diagram. Here's the database structure we ended up with as a result of the normalization we performed previously. Let me show you how to capture this structure more formally as an entity relationship diagram. First, draw the people entity, and list all of its attributes. And then, draw the phone's entity, and list all of its attributes. Next, we're going to use what's known as the crow's foot notation between the people and phone entities to represent the one to many relationship. Each of these tables has their own primary key. Now, the foreign key association always shows up on the side with the crow's foot. So, this notation indicates that on the phones side there can be zero, one, or many phones associated with a person. But on the people side, a given phone can belong to one and only one person. That's why there is only a single line coming out of the people entity. There are many tools available for creating entity relationship diagrams. Which store entity relationship models. Entity relationship diagrams are called ERDs for short. Many of these tools are free and other good ones are fairly inexpensive. Some of these tools can be used to construct a Schema from an ERD. To generate an ERD from a database or to compare the Schema associated with two database. This last one can be useful to determine if the Schema Associated with an application matches the one in a design document. That is, have the development activities led to a new schema that should be documented in the evolving design documents? Here's a very short list of some of the tools available for creating ERDs. I'll show you how to use MySQL Workbench. It's Free and easy to use. Go to MySQL.com and download MySQL workbench. Install the version that works for your operating system. Then, go to the file menu and select new model. You'll see something like this. We're going to add a new diagram, so double click on this here, and here we have a layout for constructing ERD's. Here's how you create an entity with this symbol, if I click on it here. Let's go ahead and double click on it, and then I can create the people entity. It's going to have a primary key, so I'm going to select this, and I'm going to double click here and just call this ID. I can add the other attributes so we had a first name. And we're going to say that we don't want to let this be null, you have to store first name. It's not a primary key so I left that unchecked. I can put the last name, and you don't really have to worry much that the data type that it's choosing is varchar, but don't worry about that for right now, we're just trying to document the structure. And then we're going to have an address which is going to be also a string. And so here's one table. If we want to put another one on for Phones then double click on this and we can edit this table so this will be our phones table. And again it's going to have an ID, a primary key. And you can fill the rest of this in as well, it's going to have a phone number I have been just calling that phone and that could be stored as a character. And then to create the relationship that many to one relation between the two select this close foot. And you put it on the many side and then click on the one side and it creates a relationship between the two and notice that it added the foreign key into the phones entity. It took care of the Of that when I added the minute to one relationship. So here we have a entity relationship diagram for the people phones model.
