Hey folx...
I'm developing an application to manage media (e.g. pictures in a database). The files are stored on the filesystem, MySQL is used to manage them. Now I have a table like this:
CREATE TABLE pictures (
id mediumint(9) unsigned NOT NULL auto_increment;
number mediumint(9) unsigned NOT NULL DEFAULT '1';
group smallint(3) unsigned NOT NULL;
[...],
PRIMARY KEY (id, number, group)
);
Imagine many galleries. Every gallery has an id. Every picture in that gallery has a number. Every gallery belongs to the same group. (there are other things besides galleries that 碽elong to another group but that's irrelevant)
So id=1, number=1 would be picture 1 in gallery 1. id=1, number=2 is the second picture in gallery 1 and so on.
Now when I want to add a new picture to that gallery (id=1) I need to know what number is free. auto_increment for the 'number' field doesn't work because gallery 2 (id=2) starts with picture number=1 as well...
To make matters worse I can not have a table for each gallery exclusively (auto_increment would work then).
Any ideas?
Thx in advance,
Dominique
I'm developing an application to manage media (e.g. pictures in a database). The files are stored on the filesystem, MySQL is used to manage them. Now I have a table like this:
CREATE TABLE pictures (
id mediumint(9) unsigned NOT NULL auto_increment;
number mediumint(9) unsigned NOT NULL DEFAULT '1';
group smallint(3) unsigned NOT NULL;
[...],
PRIMARY KEY (id, number, group)
);
Imagine many galleries. Every gallery has an id. Every picture in that gallery has a number. Every gallery belongs to the same group. (there are other things besides galleries that 碽elong to another group but that's irrelevant)
So id=1, number=1 would be picture 1 in gallery 1. id=1, number=2 is the second picture in gallery 1 and so on.
Now when I want to add a new picture to that gallery (id=1) I need to know what number is free. auto_increment for the 'number' field doesn't work because gallery 2 (id=2) starts with picture number=1 as well...
To make matters worse I can not have a table for each gallery exclusively (auto_increment would work then).
Any ideas?
Thx in advance,
Dominique