Perhaps the most common way to implement this design is with the "one table per owner type" scheme you mentioned (Tables for Images, "Owner A", "Owner A Images", and repeat for owners B, C, etc). Another common way to implement this is with one "central" table for Images, with the single owner's Id stored within that table. Your criteria are particularly limiting, in that an image may be associated with one and only one owner, but there are multiple types of owner. Implementing such constraints inside the database is tricky, but implementing them outside of the database is much more difficult and problematic for all the usual reasons (application doing the databases work, and what happens when someone modifies the database outside of the dedicated application?)
The following is an example of how these structures and constraints might be implemented within the database. It may appear fussy, detailed, and overly-complex, but it will do the job, and once properly implemented you would never have to worry whether or not your data was consistant and valid.
First off, all images are stored in the following table. It must be known what "type" of owner an image may be assigned to; set that in ImageType, and (as per the constraints in the later tables) the image can not be assigned to any other kind of owner. Ever. (You could also put a CHECK constraint on ImageType to ensure that only valid image types could be loaded in the table.)
CREATE TABLE Image
(
ImageId int not null
,ImageType char(1) not null
,constraint PK_Image
primary key clustered (ImageId, ImageType)
)
Next, build some owner tables. You could have any number of these, I'm just making two for sake of the example.
CREATE TABLE A
(
AId int not null
constraint PK_A
primary key clustered
)
CREATE TABLE B
(
BId int not null
constraint PK_B
primary key clustered
)
Build the association tables, noting the comments next to the constraint definitions. (This is the overly-fussy part...)
CREATE TABLE Image_A
(
ImageId int not null
constraint PK_Image_A
primary key clustered -- An image can only be assigned to one owner
,AId int not null
,ImageType char(1) not null
constraint DF_Image_A
default 'A'
constraint CK_Image_A__ImageType
check (ImageType in ('A')) -- Always have this set to the type of the owner for this table
,constraint FK_Image_A__A
foreign key (AId) references A (AId) -- Owner must exist
,constraint FK_Image_A__Image
foreign key (ImageId, ImageType) references Image (ImageId, ImageType) -- Image must exist *for this type of owner*
)
-- Same comments for this table
CREATE TABLE Image_B
(
ImageId int not null
constraint PK_Image_B
primary key clustered
,BId int not null
,ImageType char(1) not null
constraint DF_Image_B
default 'B'
constraint CK_Image_B__ImageType
check (ImageType in ('B'))
,constraint FK_Image_B__B
foreign key (BId) references B (BId)
,constraint FK_Image_B__Image
foreign key (ImageId, ImageType) references Image (ImageId, ImageType)
)
Load some sample data
INSERT Image values (1, 'A')
INSERT Image values (2, 'A')
INSERT Image values (3, 'B')
INSERT Image values (4, 'B')
INSERT A values (101)
INSERT A values (102)
INSERT B values (201)
INSERT B values (102)
View the current contents of the tables:
SELECT * from A
SELECT * from B
SELECT * from Image
SELECT * from Image_A
SELECT * from Image_B
And do some tests:
-- Proper fit
INSERT Image_A (ImageId, AId) values (1, 101)
-- Run it again, can only assign once
-- Cannot assign the same image to a second owner of the proper type
INSERT Image_A (ImageId, AId) values (1, 102)
-- Can't assign image to an invalid owner type
INSERT Image_B (ImageId, BId) values (1, 201)
-- Owner can be assigned multiple images
INSERT Image_A (ImageId, AId) values (2, 101)
(This drops the testing tables)
drop table Image
drop table A
drop table B
drop table Image_A
drop table Image_B
(Techincally, this is a good example of a variant on the exclusive type/subtype data modelling "problem".)