Creating database diagrams

- Okay, let's take a look at creating database diagrams. There's a number of different reasons for creating these database diagrams, one of which is, it really helps you to understand an existing database by doing that, by going through and creating the database diagram, so that you can see the various tables and the relationships between those tables. Another thing you can do is to document your database with a database diagram so that the other administrators can use it, or the other developers can use it to understand how the overall database works together.

So, I've got Management Studio running, and you see I have Object Explorer. And I want to show you something that you'll probably run into if you download AdventureWorks 2014 or the other databases and install it into your instance of SQL Server so that you can follow along. So, I want to create a database diagram. And if I come over here and select Database Diagram and right-click, you see I have Install Diagram Support, which I'm going to click. And now, I get an error.

It says, if you go through and read it, that there's no database owner. I'll go OK, and I'll go back up to my database and I'll right-click on it and go into Properties. And over here in General, look, owner is SQL Administrator. So, how can there be no owner? Well, it's a little bit of an odd thing. If we move on down here to Files, you see, the owner is empty down here on this Property page on file.

So we have to add that. I'm going to click the ellipsis ... And now I'll click Browse. And I'm browsing into the logins that are within this database. So, I'm going to come down here and I'm going to click on SQL_TRAIN\Administrator. That is the administrative login. It says SQL_TRAIN because I created an active directory domain and I named it, just simply, SQL_TRAIN. Normally, these would be like My Company.whatever com.biz etc.

Also, a little oddity. If you notice here, I have SA in there. SA stands for system administrator. That is because when I install SQL, I always install SQL in mixed security mode, so that I can login both with my active directory credentials or with just SQL credentials. And in that case, I can login as SA. I do that for various reasons, experiences I've had in the past when, for some reason, we couldn't get the Windows credentials to work.

So we could login as SA. And it's just a legacy thing that I tend to do. So we're going to say, let this owner now be the Administrator. And I'll say OK. And I'll check names. And we're good there. And we'll say OK. And now we've got SQL and I'm going to say OK here. And now let's see what happens. I'll go over to Diagrams. Right-click, Install Diagram Support, which is a class of objects. The database does not have one or more of the support objects. Well, I just told it that I want to install it. Do you want to create them? Yes.

Okay, and it created them and immediately, it goes into this diagram. Let's close out of that and do it again. Now I'll go Database Diagram. New database diagram, and this pops open. So the window skips this step, but just so that you see, that's what it is. The way database diagrams are laid out is alphanumeric based on the name of the table, not on the name of the schema. And you see the schema name in parentheses.

If I went over here to Tables. Close out of that. If I expand out the tables, now you see here's my schema. This is sorted by schema and then by table. But the schemas in database diagrams are sorted differently. So, if we come down here and I select, say, let's move on down and see if we can find something that's a little more interesting. I want to come down here and let's select Sales Border Detail. And I'm going to say OK.

I'm going to add it in here. And now you notice, it will allow me to add any other tables that I want. But I'm going to close. Now that I'm in the diagram, I'm going to right-click on the table. And down here it will say Add Related Tables. Which I want to see, what tables have a relationship into Sales Order Details. And I'll say Add Tables. You can see, also, other tables that are related. And again, if I right-click down here, and I say Arrange Tables for me, now the tables are arranged. Now, this is fairly handy.

So, you can see, I have Sales Order Details, Sales Order Header, and Special Offer Products. Both of these tables are related into this Sales Order Detail. And if I select it in here it will give me information and if I go in to select the actual relationship. I can right-click and go Properties and read about the properties over here. The name, it's a foreign key constraint, etc. If you're not familiar with what that is, we're going to go into that later on in this video series. Now, because I added the tables that were related to Sales Order Detail does not mean I added all the tables that were related to Sales Order Header.

So again, if I right-click on this. And say, Add Related Tables. Wow. And now, if I come down here and say Arrange Tables, you notice, it's huge. So I have all these tables and I can do the standard navigation stuff up and down. And if I click down here, see, that pops up and I can just scroll around within here. That said, this is a fairly busy little diagram. So, that's one of the reasons we don't add all of the relationships. You can see the complexity of this diagram from what I just added.

A few other things we can do with these diagrams. I can right-click down here and I could create a new table. Right in here, and we'll just say Table1. So, I've created this new table, and then, within the table I can come through and just give it a name down here and character and that's fine. I can also change the view. And, I want to delete this table cause I really don't want Table1 to be added into my database. But what I'm saying I could do down here is I could bring in a couple of tables. And then, if I want to create a new table, relating them over to this tables and I want to do it in the data diagram, I can.

I can right-click down here. And notice I can copy the diagram to the clipboard and then I could paste it into other things, such as Word or whatever it is I'm trying to show that. Now, I can show relationship labels and collecting that, it will pop up and show me the various labels that I have. And I can move that through. That's said, it's still a little confusing when you have this many tables on it. I have to standard zoom and let's go to 25%.

So, you see now, that's a little better, maybe too much zoom. And so 50% in here and you've got this. And as you can see, this might be something that you want to put into a Powerpoint so that you will be able to explain it. Now, I went through and added these tables that were existing. You can also create a diagram and then within the diagram, come through and create a series of tables within your database, adding the primary keys and the foreign keys, and the column names, etc.

And you can use this as you're going along, doing your development, as not only the documentation of your development, but within the team you can show where the plan currently is and what it is we're trying to do, so that if you had different people creating different tables, they would understand their portion of the project. I happen to like database diagrams and I use them often.