Table Design and Naming Convention

Dave Merton
A free video tutorial from Dave Merton
Senior SQL Server Database Developer
4.1 instructor rating • 3 courses • 4,733 students

Learn more from the full course

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.

02:00:51 of on-demand video • Updated March 2014

  • By the end of this course you should have a very clear understanding of how to design, create, manage and maintain the different types of database tables.
  • This course will also whet your appetite for stored procedures. You will learn from real world examples how to create table specific Stored Procedures to facilitate the INSERT, UPDATE and DELETE functions.
  • This course will teach you many different concepts and techniques; however you will also see several code examples of multiple techniques in action at the same time. This will provide you with a real world taste of what you can do in SQL Server and will catapult (fast track) you past your colleagues.
English [Auto] In this next video we're going to be talking about how to create a set of tables and look at tables and as we do this we're actually not going to create what we're just going to talk more about that theory are following that you will show very quickly how to create one management studio. But there's a few things that you really want to make sure that you understand when you're creating lookup tables if you follow these rules and these paradigms will be a lot happier. The first thing that I wanted to mention let's just take a graphical look in Excel of what a table might look like. This is just we'll call it a template. The letter X Inish these cases and fields I like to use a prefix and it's something unique usually for every table like for a table on units of measure. I used the letter U. That kind of thing I always called the auto number increment key and then I always have to text fields when I call display when I call description and then I have one called a visible one called Order one code created and one called modified. I use those fields as a minimum as a minimum in any set up table. I could add other fields to it and sometimes you have to sometimes you have to. The reason I use these are all different reasons created is when the record was created in single server. Modified is something you manage afterwards and that it changes something. You may also want to track what was and what machine the change came from. Order is really something that you would want to use if you're trying to supersede the order of what you're displaying possibly in a dropdown because you want to override the alphanumeric and visible might just be if you have values that you need to keep in the system for historical reasons but you may want to suppress them from future drop downs future situations where people can actually pick that option. Here's what this might look like specifically for units. Just basically put the letter you on the front of everything here. Notice the order 100 100 200 200 100 by changing this. I can query this information so that anything with a few hundred comes last. So maybe in this case they want to use anything that's non-metric first and then put all the metrics. And that's why you would do something like that. Let's take a look now more closely display and description. Why would you need to text fields. Well see how there is a short movement along in and look at tables. There's always this concept. I'm going to go over something and I want you guys to understand this is from after setting up thousands and thousands of tables what you want to do is understand something there's always reasons why you have a long name and a short and sometimes maybe on it you might want to put this next to you know a measurement instead of something long like this. Sometimes you need a short name sometimes you need a long day maybe you're printing out labels or something in all the pens. But if you were creating different set of tables at different times you might run the risk of saying well there's a short name or a long game or there's a short name and a full name or you know there is an atomic symbol and an atomic name. Really though as you mature in database development it will be of you to do this. Any time that you're making a set up table and you're having displayed fields are text fields call them the same thing. Even if you change the prefix a little bit try to call them the same thing always say something underscore display something that underscore a description. Even if that seems a little strange right now. But the reason why you want to do that is that when you're picking from all these different set of tables and you're writing all these complicated join areas laying around you obey be sitting there saying What did I call that field. You remember something underscore this way or that something underscores description and you'll know what the prefix is because it's usually the first letter of the table in the bakery. So it's pretty simple. Here's what it would look like a little more graphically as a template. You have a key it's a primary here it's an auto increment. You've got your display and your description. I always make them 2000 characters. You'll never use that much but with a pair of pair. You only pay for what you use. So why make it 25 and then have something that's going to get truncated. Visible is always a bit. And I want to play true order is always an end and I defaulted to something ridiculously high. The reason why you would do that is because if you don't have to fold and if you don't have a high to hold when you start setting orders for things like the first one and the second one is 20 anything that. No that hadn't been set yet. Actually goes to the pot. And you don't want that to happen. So you basically put everything at the end of the line until you start changing it created. Isn't the all value of the current date and time when the record is created. And in our next video we'll actually show how to do this in management studio.