how to design the scnema for a photo manipulation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

how to design the scnema for a photo manipulation

Post by davidklonski »

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:

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

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

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;
can anyone point me to some drawbacks in the design and offer some better solutions?

thanks
Post Reply