Hello
I am trying to design a schema that will allow me to manage photos.
I am stuck in deciding what's the best schema would be.
Here is what I have so far:
Each photo has some info which is defined in the image_info table like so:
create table image_info (
image_id int unsigned not null auto_increment,
title varcher(255) not null,
place varcher(255) not null,
description longtext not null,
primary key (image_id)
);
Each photo may have multiple (the number is unlimited) thumbnails stored in the database. Information about the thumbnails is stored in the raw_image table:
create table raw_image (
image_id int unsigned not null auto_increment,
size int not null,
width int not null,
height int not null,
src varchar(255) not null,
image_info_id int not null,
reverse_image_id int not null,
primary key (image_id)
);
An entry in the raw_image table is connected to the image_info table via the image_info_id column (basically I need to do an inner join on these attributes to connect between the acutal image (raw_image) and the info on the image (image_info)
Some of the photos may have a reverse side (this is a scan of the backside of the front image). As such, a reverse image will have its own entry in the raw_image table. The front raw_image will be connected to its reverse via the reverse_image_id attribute (defined in raw_image). I would have to use a left join on this, as most of the images will not have a reverse side.
Here is a sample query that will extract some info (very minimal) on an image:
select image_info.*, front.Image_ID as front_ID, reverse.Image_ID as reverse_ID
from image_info
left join image_data as reverse ON front.Reverse_Image_ID = reverse.Image_ID
inner join image_data as front ON image_info.image_id = front.Image_Info_ID;
can anyone point me to some drawbacks in the design and offer some better solutions?
thanks
