CODEWITHSUNDEEP
databasedatabase-design
GibboK
GibboK

Reputation: 73918

Data Base Design for Images

I would like your advice regarding Data Base design. I have 4 different data elements (tables A,B,C,D) example: A - Contents B - Categories C - Authors and D - Images

Every record in tables A,B,C could have associated 1 or more different Images in Table D, BUT for every image in D must be uniquely associated only a record in A,B,C. This means that images cannot be shared (between others tables).

My idea was to create different Image tables for every data elements, using ONE to MANY association type. Example: Content --> Image-Contents and Categories --> Image-Categories

Questions? My database design is a good one?

Since Tables "Image-Contents" and "Image-Categories", could have similar property like "File-Url" or "Image-Title", I was concerning if could be exist a most suitable database design solution.

Thanks for your time

Upvotes: 4

Views: 4130

Answers (4)

zaf
zaf

Reputation: 23244

Yes, you're looking in the right direction.

Keep your current setup of the four tables and then create 3 more that hold only metadata that tells you the linking between, for example, the content table and the image tables.

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

And so on.

Upvotes: 1

pascal
pascal

Reputation: 3365

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

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40319

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

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85056

I think you would want a table that maps each of ABC to an image. For example:

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

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

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

It may seem a little cumbersome but i think this is the normal form.

Upvotes: 2

Related Questions