CREATE TABLE [dbo].[Facility](
	[cFacilityID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED ,
	[cFacilityName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Department](
	[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES [dbo].[Facility] ([cFacilityID]) ON UPDATE CASCADE ON DELETE CASCADE,
	[cDepartmentID] [nvarchar](3) NOT NULL,
	[cDepartmentName] [nvarchar](50) NOT NULL,
 PRIMARY KEY CLUSTERED ( [cFacilityID], [cDepartmentID])
)
GO
CREATE TABLE [dbo].[User](
	[cUserID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED,
	[cUserName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Addresses](
	[pkAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[cAddress] [nvarchar](255) NULL,
	[cZip] [nvarchar](5) NULL,
	[cEmail] [nvarchar](255) NULL
)
GO

quote:

---

Originally posted by LoztInSpace

5. Typed intermediate table, i.e. an address ID and facility ID, another with address ID and departmentID etc. Essentially its a combination of 1 and 4. This gives you RI and minimises the duplication of the structure. You might also need to include a sequence number to give you uniqueness if you allow more than one address per type.

---

CREATE TABLE [dbo].[FacilityAddress](
	[pkFacilityAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES Facility (cFacilityID) ON UPDATE CASCADE ON DELETE CASCADE,
	[fkAddress] [int] NOT NULL FOREIGN KEY REFERENCES Addresses (pkAddress) ON UPDATE CASCADE ON DELETE CASCADE,
)

create table AddressType 
(
	TypeCode nvarchar(10) not null primary key,
	TypeName nvarchar(100) not null
)

create table Address
(
	AddressId int identity not null primary key,
	AddressType nvarchar(10) not null references AddressType(TypeCode) on delete cascade,
	FacilityID nvarchar(5) references Facility(cFacilityID), -- can't cascade this one ...
	DepartmentFacilityID nvarchar(5),
	DepartmentID nvarchar(3),
	UserID nvarchar(5) references [User](cUserID) on delete cascade,
	AddressData int, -- ints are short to type!
	foreign key (DepartmentFacilityID, DepartmentID) references Department(cFacilityID, cDepartmentID) on delete cascade,
	check (case when FacilityID is null then 0 else 1 end +
		case when DepartmentID is null then 0 else 1 end +
		case when UserID is null then 0 else 1 end = 1 ),
	constraint address_uc unique (AddressType, FacilityID, DepartmentFacilityID , DepartmentID, UserID)
)

-- create some address types:
insert into AddressType values ('HOME','Home Address')
insert into AddressType values ('BILL','Billing Address')

-- add some sample data:
insert into Facility values('F1','Facility 1')
insert into Department values('F1','D1','Dept 1')
insert into [User] values ('u1','User 1')

-- add a home address for facility F1:
insert into Address (FacilityID, AddressType, AddressData)
values ('F1','HOME',0)

--ERROR: disallow two HOME addresses for this facility:
insert into Address (FacilityID, AddressType, AddressData)
values ('F1','HOME',1)

-- adding BILLING address for the facility:
insert into Address (FacilityID, AddressType, AddressData)
values ('F1','BILL',1)

-- ERROR: cannot have an address with more than 1 entity 
insert into Address (FacilityID, UserID, AddressType, AddressData)
values ('F1','U1','HOME',1)

-- add user address:
insert into Address (UserID, AddressType, AddressData)
values ('U1','HOME',1)

-- delete User 'U1', verify User addresses are gone only
delete from [User] where cuserID = 'u1'
select * from Address

quote:

---

Originally posted by jsmith8858

I've experienced the same issue in the past, it can be frustrating and this type of design is difficult.

---

quote:

---

Originally posted by jsmith8858
One thought about multiple addresses: Consider creating an AddressType table to be sure that you constrain the multiple addresses to one per type per entity. This way, the multiple addresses per entity will have clear, unambiguous meaning. Not always applicable, but I just about always do this and it works well.

---

quote:

---

Originally posted by jsmith8858
Just some ideas. Notice the CHECK constraint that only allows one set of values in the FK columns. Also, note that we need to separate the two FacilityID columns, one for Facilities and the other as part of the Department FK.

---

quote:

---

Originally posted by LoztInSpace

...although I would not have a surrogate PK.

---

quote:

---

Originally posted by LoztInSpace

Perhaps a trigger is in order here (I hate them) but personally I would secure all tables and ensure all operations are done via SPs anyway. Then you can implement whatever you need.

---

quote:

---

Originally posted by jsmith8858

I would go with Option 3 in this case. You can have cascading RI on everything but Facility, I believe, and for that one item you are probably better off just using a trigger.

---

CREATE TABLE [dbo].[Facility](
	[cFacilityID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED ,
	[cFacilityName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Department](
	[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES [dbo].[Facility] ([cFacilityID]) ON UPDATE CASCADE ON DELETE CASCADE,
	[cDepartmentID] [nvarchar](3) NOT NULL,
	[cDepartmentName] [nvarchar](50) NOT NULL,
 PRIMARY KEY CLUSTERED ( [cFacilityID], [cDepartmentID])
)
GO
CREATE TABLE [dbo].[User](
	[cUserID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED,
	[cUserName] [nvarchar](50) NOT NULL
)
GO

create table AddressType 
(
	TypeCode nvarchar(10) not null primary key,
	TypeName nvarchar(100) not null
)
GO

create table Address
(
	AddressId int identity not null primary key,
	AddressType nvarchar(10) not null references AddressType(TypeCode) on delete cascade,
	FacilityID nvarchar(5) references Facility(cFacilityID), -- can't cascade this one ...
	DepartmentFacilityID nvarchar(5),
	DepartmentID nvarchar(3),
	UserID nvarchar(5) references [User](cUserID) on delete cascade,
	AddressData int, -- ints are short to type!
	foreign key (DepartmentFacilityID, DepartmentID) references Department(cFacilityID, cDepartmentID) on delete cascade,
	check (case when FacilityID is null then 0 else 1 end +
		case when DepartmentID is null then 0 else 1 end +
		case when UserID is null then 0 else 1 end = 1 ),
	constraint address_uc unique (AddressType, FacilityID, DepartmentFacilityID , DepartmentID, UserID)
)
GO