Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-08-20 : 10:52:56
|
| I need a solid way to identify the user that is "Administrator" of every organization in a company. (small point - An Administrator does not need to be contrained to Administer his / her organization. It may be a different organization.)A compact way of describing this in the database would be:Table Org has a required column which is an identifier of an Administrator.Create Table Org (OrgID [INT] IDENTITY (1,1) PRIMARY KEY ,OrgName [varchar] (30) NOT NULL , Admin [INT], FOREIGN KEY Users (UserID) NOT NULL )BUT:Table Users has a required column which is an identifier of an Organization. (This is the User's organization - nothing to do with Administrative roles.)CREATE Table Users (UserID [INT] IDENTITY (1,1) PRIMARY KEY NOT NULL ,Org [varchar] (30), FOREIGN KEY Org (OrgName) NOT NULL )This creates a referential loop. When both tables are empty, neither table can be the first to receive a row since the new row cannot satisfy the NOT NULL constraint with the other table empty. However, this seems to be the most compact solution, probably the fastest executing (if the tables could be populated).Every way out of the loop results in compromising some other aspect of the database:1 - Change the design, remove the column "Admin" in table Org. Instead, add a column to Users called "Admin" that contains OrgName. This results in much more data, and a longer search to find the Administrative user for an organization.2 - Remove the "NOT NULL" constraint on the COLUMN Admin in Table Org. This allows populating the "Admin" column after the row is inserted. It also opens the door for the column to contain a not null value. But maybe I can live with this.I'm going to opt for the 2nd solution unless there's a better one.SamC |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-20 : 16:55:34
|
| One and only one admin per org?How about another table to link admin users to org - then you can cater for multiple admins and someone taking over the job for a period (holidays?).Could call it a role table so that users can have multiple roles.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Referential Integrity Loops