Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
To create new tables, we need to drill into the database. We want to add those tables too, then expand the Tables folder and just right-click it and select New Table. Now you would expect to first say what the table is called but in fact your cursor is blinking away now at a column name. That's because it's giving you this temporary title for the table here, Table_1. We will get the chance to change that in just a minute but I'm going to describe a few columns because apparently that's what I have to do here.
Let's say what I wanted to do was create a table to hold employee information. So I have got things like employee first name, employee last name, date joined, birth date, phone, that kind of thing. So I'm going to just create a few simple columns here. The first would be FirstName. Again I'm splitting this up into first name and last name rather than just having a whole name. Then when I hit Tab, it gives me a drop-down of Data Type. This is, again, the type of data that this is going to hold.
If I click the drop-down box, we have a whole bunch here. We have got geography and geometry, is it decimal, is it date time, is it a bit? We will talk about quite a lot of these different data types but what I am going to change it to is nvarchar(50), and what this means is a variable length text field, variable length character field that can store up to 50 characters, all Unicode data. I am going to talk more about those data types in a later movie but this will get us started.
The next column is do we allow nulls or not. Can this field be blank? A lot of this is going to be done to your particular business process but let's say that I imagine everybody in my company has a first name. I am going to say no, I don't allow nulls. Same with LastName and I will also make that nvarchar(50). Now I will just go through and do a couple of different things. Title can be nvarchar(50). HireDate will be a date.
In fact, what you will is there are multiple date types. There's datetime, datetime2, but date is fine for me. BirthDate, that I will also have be a date. Phone, that's going to be an interesting one because we'll see how that one could be stored. We might want to allow people to put in dashes, for example, or even letters, who knows. So I am going to change that to nvarchar. It certainly won't be 50 characters long but I'll give it say 20 or 25. 20 will do.
And let's perhaps say we have an employment status field. I am going to call it Status and this might just mean they're employed or they're not employed. It's just a yes or no. Sure, you could store that as the text word Yes or the text word No in a character field but what I am going to do instead is have a field that is a bit and this basically is a little flag, the equivalent of a checkbox. It's either true or false. And I would say that that has to be required. So let me imagine for right now that this might be it.
So this is all I'm going to have for my employee table. I can assure you it's really not complete yet but let's pretend that it is. What I am going to do is hit Ctrl+S to save this. That's going to now tell me finally, enter a name for the table and I'm going to call this Employee. Now there is a bit of a tedious debate over whether to use plural or singular names for your table. Should this be an employees table that contains multiple rows each representing an employee or is it employee singular? I've worked in places where there's been a standard one way or the other.
I really don't care. I just think you need to be consistent. If, for example, I'll save this as a singular table name and if you look at say what Microsoft is doing with AdventureWorks, you can see that this appears to be singular. It's Product, Location, Culture, Address, Contact, rather than contacts, products and so on. So I'll just take my lead from them at this particular moment. And the question is so what, what can we do? Well, now that we've got at least one table created and I can see that in my Two Trees Test database.
It's showing up as dbo.Employee and this again is using the dbo as the default schema, the default container, if you will, for our tables. I'm just going to leave most of our tables as that, which is perfectly acceptable for our case. If you get into more complex database development, such as something on the size of the AdventureWorks one, you may want to start grouping your tables together into different schemas. Schema don't really mean anything other than a way to group and understand objects in your database and potentially to give them different owners.
We are not really going to use them in this course. So you'll see a lot of dbo for our tables. One thing I could do at this point is open on my Database Diagrams panel. It's going to prompt me that the database does not have one or more of the support objects required to use database diagramming and do you want to create them. Well, yes, I do. And the question is what does that mean? Well, right now nothing. I have got the support files to create them. I haven't made one yet. If I click this, and then right-click, say New Database Diagram, it's going to throw open the diagram and then pop up this prompt that says "Well, I'm going to give you the option to add your existing tables to the diagram." Of course, I only have one table so I might as well add that. I am going to click Close.
Then we can see that we are actually looking at a diagram of our employee table with all our different columns listed in it. Now one thing you can do if you are so inclined is you can actually use this database diagram technique to create new tables. I could right-click anywhere in the blank area and actually say that I want to make a new table. It's not going to ask me to give a name for this table. I will call it Product. And in this, I can directly starts typing and I will zoom back into 100%, column names such as a ProductID. Let's call that the nchar data type of 10 is fine.
ProductName, nvarhcar(50). Well, let's say we have long products. Maybe this will be 200 and so on. Now do bear in mind, you are not just creating a pretty diagram here. I can see that I have got this little asterisk after the name of product and what that really means is its detecting there is a change made to this table definition that has not been saved. But if I were to save it, I am just hitting Ctrl+S, I am saving the diagram here and that's kind of a little deceptive sometimes because as I'm saving that, it's actually telling me, "Well the following tables will be saved to your database, " do you want to continue? Yes I do.
That has created not only a diagram but whatever I did on the diagram really did affect the database. So I now have dbo.Product. I could continue to edit it over here in the diagram or I could right-click over here and say Design, which would allow me to jump into the standard view of editing. As you'll see below, anytime I have one of these columns selected, I do have a whole host of more properties that I can change about it. And we will visit some of these a little later on.
But that's the general process of how we start to add tables to our database and how we start to manipulate columns in those tables.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.