About large database projects (ADP)

A multiuser environment is one in which other users can connect and make changes to the same database that you are working with. As a result, several users might be working with the same database objects at the same time. Thus, a multiuser environment introduces the possibility of your database diagrams being affected by changes made by other users, and vice versa. Such changes could include changes to copies of your database diagrams, other users’ database diagrams that share database objects with your database diagrams, or the underlying database.

A key issue when working with databases in a multiuser environment is access permissions. The permissions you have for the database determine the extent of the work you can do with the database. For example, to make changes to objects in a database, you must have the appropriate write permissions for the database. For more information about permissions in your database, see the SQL Server documentation.

As one of multiple users, you may need to address any of the following issues when working with the Database Designer:

• Ownership of database objects

• Diagrams affected by another user's changes

• Database objects deleted by another user

Each database object is owned by either a user or a role. If the owner is a role, the object is co-owned by every user belonging to that role.

Each object also has privileges associated with it. A privilege grants a particular authority to an object for either an individual user or for all users belonging to a particular role.

Depending on the privileges defined in the database, you can view objects that you do not own. For example, you can include on a database diagram an object owned by another user. When such an object appears on a database diagram or in the Table Designer, it is labeled with the owner name as well as the object name.

In a multiuser environment, your database diagram can be affected by changes other users have saved to:

• Your database diagram, which other users changed since you opened the diagram.
• Database objects shared between database diagrams.
• The database.

For example, your database diagram might contain a table that another user deleted or renamed. In such a case, your database diagram will no longer reflect the current state of the database. When you attempt to save your database diagram or selected tables, the Database Changes Detected dialog box notifies you that the database has been updated since you opened your diagram.

This dialog box also displays a list of database objects that will be affected as a result of saving your database diagram or selection. At this point, you can take one of these actions:

• Save your database diagram or selection and update the database with all the changes in the list.    This action will also affect other database diagrams that share the same database objects.
  For example, suppose you edit the au_id column in the titleauthors table on your database diagram and another user’s database diagram contains the authors table which is related to the titleauthors table by the au_id column. Saving your database diagram will affect the other user’s database diagram. Similarly, suppose that another user defined a check constraint for the qty column in the sales table. If you delete the qty column and save the sales table, the other user’s check constraint will be affected.
• Cancel the save action.    You can then close the database diagram without saving it. When you reopen the database diagram, it will be in synch with the database.
• Save a list of the changes.    You can save the list of database changes shown in the Database Changes Detected dialog box to a text file so that you can investigate the cause of other users’ changes.
  For example, if another user edited a table that you marked for deletion, you may want to research whether the table should be deleted before updating the database.

In a multiuser environment, deleting database objects from the database can affect other users. If another user deletes a database object that appears in your database diagram, the effect of the deletion on your database diagram depends on where the object was deleted.

Note  Recreating a deleted object creates a new definition of that object in the database; it does not restore the data that was deleted when the object was deleted.

For example, if the titles table is deleted in the database and you have unsaved changes to the titles table in your database diagram, then saving your database diagram will create a new titles table in the database. Any data that existed in the titles table before it was deleted is not restored.