Database Designer

The Database Designer is a visual tool allowing you to create, edit, or delete database objects for databases while you’re directly connected to the database in which those database objects are stored.

You interact with the server database using database diagrams. Database diagrams graphically represent the tables in your database. These tables display the columns they contain, the relationships between the tables, and indexes and constraints attached to your tables. When you establish a connection to a database, you can use database diagrams to:

View the tables in your database and their relationships.
Perform complex operations to alter the physical structure of your database.

You can make changes freely in the database diagram without affecting the underlying database. When you modify a database object through a database diagram, the modifications you make are not saved in the database until you save the table or the database diagram. Thus, you can experiment with "what if" scenarios on a database’s design without permanently affecting its existing design or data.

You can then either:

Save the changes to selected tables or the database diagram and have the changes modify the server database. For more information, see Saving Selected Tables.
Discard your changes.
–or–
Save a change script that contains the SQL code generated by your changes to the diagram. If you save a change script instead of saving your changes to the database, you can edit the change script in a text editor and then apply the modified script to the database.
You can also choose to automatically create a change script each time you save your database diagram or any database object in your diagram. This provides you with a record of the changes you made to the database. For more information about change scripts, see Saving a Change Script.

You control the timing, type, and extent of the changes to your database by choosing how changes to the database diagram affect the server database.

Creating and Managing Database Diagrams

You can use database diagrams to:

Manipulate database objects without having to write SQL code.
Visualize the structure of your database tables and their relationships.
Provide different visualizations of complex databases.
Experiment with database changes without modifying the underlying database.
Create new tables, indexes, relationships, and other constraints.
Alter the structure of your database.

For example, you can create a database diagram for your company’s payroll department that shows only tables that hold employee benefits information. Or you can create a diagram for your development team that shows only those tables that are used in a specific application module.

You can change the size, shape, and position of objects in your diagram without affecting their definitions in the database. When you save your database diagram, the layout of your diagram is preserved as well as any changes you made to the object definitions in your diagram.

The Database Designer does not require additional security features set up separately from the security features in your database. Database diagrams are saved in the dtproperties or MicrosoftDTProperties table of your database.

For information about	See
Creating, saving, formatting database diagrams	Working in Database Diagrams
Working with multiple users and reconciling changes with a modified database	Working in a Multi-User Environment
Modifying the database by saving tables and saving change scripts	Controlling Database Changes
Creating, editing, deleting database objects in a database diagram	Working with Database Objects

Creating and Modifying Database Objects

Tables are the basic building blocks of database diagrams. Every diagram is made up of one or more tables and the various objects related to those tables.

Each table has a set of columns that contain information about the type of data that you will store in the table. You must define at least one column in order to save a table in the database. Each column must have a column name, an assigned data type, and an assigned length. These and other properties can be defined in your database diagram. You modify tables and their columns directly in a database diagram.

A table can also have a set of constraints and indexes. Because constraints and indexes are defined for a specific table, they are treated as properties of that table in a database diagram. You use the property pages to create, modify, or delete these database objects for a table.

You can use the Database Designer to create and modify database objects, including:

For more information about creating and modifying database objects, see Working with Database Objects.