Designing a database can be a daunting task, especially on a large scale. There are several ways inside of SQL Server Management Studio to do design. You could create your database and right click on the ‘Tables’ node and enter all the information there. Column names are listed out followed by their data types with all the properties being set below.
This is a quick and easy way to edit your tables as well as design them. The issue occurs when you start to set table relationships. A lot of people find it difficult to keep track of all the table relationships if they can’t see how they are connected. Luckily there is an alternate way to design your database: Design Diagrams.
The Database Diagrams section of any database will provide an environment that is even easier to create tables and draw relationships. Simply right click on Database Diagrams and select New Database Diagram. From there right click to add new tables to your empty workspace. Simply name your table and start naming columns. The properties pane that was at the bottom of the screen is now housed on the right side of the screen. Here you can set Identity Columns, change descriptions, and set data types. Creating table relationships has never been easier. Simply click and drag the column name from Table1 and drop it on top of Table2. A window will open asking you to name the relationship, and set the foreign key in Table2. A line will then connect both tables, one of which will have a gold key to signify the table containing the primary key.
Another great part of this design mode is being able to print a copy of the diagram if you so choose. This came in handy when it came time to generate data for this particular project as the order you put data in one table has an effect on all joining tables. You can easily see all the information that needs to be added so you can make an informed decision on which table to populate and when. Using this design method probably saved me an hour or two of trying to figure out what relationships I had already created without having to back track my work… and this database only has 20 tables in it. Imagine the savings on a database with 50 or more tables.
