At a logical modeling POV your descriptions highlights the fact that the entities like person, company, conference etc have a common trait: they have zero, one or more addresses. If you would model this as a class hierarchy, perhaps you would create an Addressable class and have person, company and conference inherit from this Addressable class. You can apply the same reasoning to your data model and have an addresable table with an addressable_entity_id. The person, company, conference entities would 'inherit' this table. There are three established ways to implement table inheritance:
So you could model your tables like this:
create table Addresses (AddressId int not null identity(1,1) primary key, ...);
create table Addressable (AddressableId int not null identity (1,1) primary key, ...);
create table AddressableAddress (
AddressId int not null,
AddressableId int not null,
constraint AddressableAddressAddressId
foreign key (AddressId) references Addresses(AddressId),
constraint AddressableAddressAddressableId
foreign key (AddressableId) references Addressable(AddressableId));
create table Person (PersonId int not null identity(1,1) primary key,
AddressableId int not null,
...,
constraint PersonAddressableAddressableId
foreign key AddressableId references Addressable (AddressableId));
create table Company (CompanyId int not null identity(1,1) primary key,
AddressableId int not null,
...,
constraint CompanyAddressableAddressableId
foreign key AddressableId references Addressable (AddressableId));
Of course you have to find the right balance between absolute relational normal form and actual usability. In this scheme I propose for instance in order to insert a new Person one has to first a row in Addressable, get the AddressableId and then proceed and insert the person. This may or may nor work. BTW, there is a way to do such an insert in one single statement using the OUTPUT clause to chain two inserts:
insert into Addressable (AddressableType)
output inserted.AddressableId, @FirstName, @LastName
into Person (AddressableId, FirstName, LastName)
values (AddressableTypePerson);
But now is difficult to retrieve the newly inserted PersonId.
sqlandserver