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:
Code: Select all
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)
);Code: Select all
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)
);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:
Code: Select all
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;thanks