Database Design for Images

Question

I would like your advice on database design. I have 4 different data elements (tables A, B, C, D): A - Content B - Categories C - Authors and D - Images

Each entry in tables A, B, C can contain 1 or several different images in table D, BUT for each image in D only an entry in A, B, C should be unambiguously associated. This means that images cannot be exchanged (between other tables )

My idea was to create different image tables for each data item using the ONE to MANY association type. Example: Contents → Image Contents as well as Categories → Image Categories

Questions? Is my database design good?

Since the tables "Image-Contents" and "Image-Categories" can have a similar property, for example, "File-Url" or "Image-Title", I meant if there was a most suitable solution for designing databases.

thank you for your time

Answer 1

, " " ( , "", " " B, C ..). "" , . , , . , (, , , - ?)

, . , , , , .

-, . , "" ; ImageType ( ) . -. ( CHECK ImageType, , .)

CREATE TABLE Image
 (
   ImageId    int      not null
  ,ImageType  char(1)  not null
  ,constraint PK_Image
    primary key clustered (ImageId, ImageType)
 )

. , .

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   
 )

, . ( ...)

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)
 )

INSERT (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)

:

SELECT * from A
SELECT * from B
SELECT * from Image
SELECT * from Image_A
SELECT * from Image_B

:

--  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)

( )

drop table Image
drop table A
drop table B

drop table Image_A
drop table Image_B

( , / "".)

Answer 2

, , ABC . :

Content   -> ContentImages -> Images
---------    -------------    ------
ContentId    ImageId          ImageId
             ContentId

---

Categories -> CategoryImages   -> Images
----------    ----------------    ------
CategoryId    ImageId             ImageId
              CategoryId

---

Authors    -> AuthorImages     -> Images
----------    ----------------    ------
AuthorId      ImageId             ImageId
              AuthorId

, , .

Answer 3

create table A (IDA int not null, primary key(IDA));
create table B (IDB int not null, primary key(IDB));
create table C (IDC int not null, primary key(IDC));

create table Image(IDI int, A int null, B int null, C int null, Contents image,
foreign key (A) references A(IDA),
foreign key (B) references B(IDB),
foreign key (C) references C(IDC),
check (
(A is not null and B is null and C is null) or
(A is null and B is not null and C is null) or
(A is null and B is null and C is not null)
));

Answer 4

, .

Keep the current setting of the four tables and then create 3 more that contain only metadata that tells you the relationship between, for example, the content table and the image tables.

For example, the image content table will contain the columns: id, content-id, image-id

Etc.