Suggestions for a small database layout...Very simple

[Summasummarum]

Hi ng,

I need some input/suggestions for a very small layout.

The situation: Some groupings of thumbnails. For every picture (thumbnail)
there is a "big" picture. Thats it basically :) On the front the scenario is
this:

A user clicks "Autumn". The user is presented with the "Autumn" thumbnails.
If he click a thumbnail, the corresponding big image is displayed. Well, u
get the picture :)

Hoe is this most efficiently implemented in table-layout? I mean....do I
create a table called "images" and have a column called "is_thumb"? Or do I
better make 2 tables...Or better make a "relation/type" table too?

--
Thanks,
/Summa

[John Gilson]

Re: Suggestions for a small database layout...Very simple

"Summasumma rum" <Summasummarum@ hotmail.com> wrote in message
news:3fc63d05$0 $159$edfadb0f@d text02.news.tel e.dk...[color=blue]
> Hi ng,
>
> I need some input/suggestions for a very small layout.
>
> The situation: Some groupings of thumbnails. For every picture (thumbnail)
> there is a "big" picture. Thats it basically :) On the front the scenario is
> this:
>
> A user clicks "Autumn". The user is presented with the "Autumn" thumbnails.
> If he click a thumbnail, the corresponding big image is displayed. Well, u
> get the picture :)
>
> Hoe is this most efficiently implemented in table-layout? I mean....do I
> create a table called "images" and have a column called "is_thumb"? Or do I
> better make 2 tables...Or better make a "relation/type" table too?
>
> --
> Thanks,
> /Summa[/color]

-- A row for each image, whether full or thumbnail
CREATE TABLE Images
(
image_name VARCHAR(25) NOT NULL PRIMARY KEY
)

-- Relationship between full image and its thumbnail
-- Assume a full image can have multiple thumbnails
CREATE TABLE ImageThumbnails
(
full_image_name VARCHAR(25) NOT NULL
REFERENCES Images (image_name),
thumbnail_image _name VARCHAR(25) NOT NULL PRIMARY KEY
REFERENCES Images (image_name)
)

-- A thumbnail could be found in multiple categories
CREATE TABLE ImageCategories
(
image_category_ name VARCHAR(25) NOT NULL, -- example: 'Autumn'
thumbnail_image _name VARCHAR(25) NOT NULL
REFERENCES ImageThumbnails (thumbnail_imag e_name),
PRIMARY KEY (image_category _name, thumbnail_image _name)
)

Regards,
jag

[Summasummarum]

Re: Suggestions for a small database layout...Very simple

John Gilson wrote:[color=blue]
> Regards,
> jag[/color]

Thanks for your suggestion. Interesting. I havent really thought of the
solution like that. Suppose it was given that theres allways 1 - and only
one - thumbnail pr Image (And vice versa)...would I then violate any "rules"
if I make a table like this:

CREATE TABLE Images
(
thumbnail_image _name VARCHAR(25) NOT NULL PRIMARY KEY
full_image_name VARCHAR(25) NOT NULL
)

?

To honest I dont like the above. Suppose the requirements changes and 2
thumbs pr Image is required...I kinda like the approach like this:

CREATE TABLE Images
(
image_id INTEGER NOT NULL PRIMARY KEY
image_name VARCHAR(25) NOT NULL
)

CREATE TABLE Image_Type
(
image_type_id INTEGER NOT NULL PRIMARY KEY
image_type INTEGER NOT NULL
)

CREATE TABLE Image_type_imag es_relations
(
image_type_rela tion_id INTEGER NOT NULL PRIMARY KEY
image_id INTEGER NOT NULL REFERENCES Images (image_id)
image_type_id INTEGER NOT NULL REFERENCES Image_Type (image_type_id)
)


In the above....Image_ Type.image_type would then be an integer say 1 for a
thumbnail, and 2 for a big Image.
This way I think I am ready for future upgrades on the functionality.. .What
do you say about this approach?

/Summa



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27-11-2003

[John Gilson]

Re: Suggestions for a small database layout...Very simple

"Summasumma rum" <Summasummarum@ hotmail.com> wrote in message
news:3fc7a471$0 $174$edfadb0f@d text02.news.tel e.dk...[color=blue]
> John Gilson wrote:[color=green]
> > Regards,
> > jag[/color]
>
> Thanks for your suggestion. Interesting. I havent really thought of the
> solution like that. Suppose it was given that theres allways 1 - and only
> one - thumbnail pr Image (And vice versa)...would I then violate any "rules"
> if I make a table like this:
>
> CREATE TABLE Images
> (
> thumbnail_image _name VARCHAR(25) NOT NULL PRIMARY KEY
> full_image_name VARCHAR(25) NOT NULL
> )
>
> ?
>
> To honest I dont like the above. Suppose the requirements changes and 2
> thumbs pr Image is required...I kinda like the approach like this:
>
> CREATE TABLE Images
> (
> image_id INTEGER NOT NULL PRIMARY KEY
> image_name VARCHAR(25) NOT NULL
> )
>
> CREATE TABLE Image_Type
> (
> image_type_id INTEGER NOT NULL PRIMARY KEY
> image_type INTEGER NOT NULL
> )
>
> CREATE TABLE Image_type_imag es_relations
> (
> image_type_rela tion_id INTEGER NOT NULL PRIMARY KEY
> image_id INTEGER NOT NULL REFERENCES Images (image_id)
> image_type_id INTEGER NOT NULL REFERENCES Image_Type (image_type_id)
> )
>
>
> In the above....Image_ Type.image_type would then be an integer say 1 for a
> thumbnail, and 2 for a big Image.
> This way I think I am ready for future upgrades on the functionality.. .What
> do you say about this approach?
>
> /Summa
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.545 / Virus Database: 339 - Release Date: 27-11-2003[/color]

The reasoning behind my suggestion was the following:
1. An Images table representing each image, regardless of size or use.
So each image has a row in this table with any information intrinsic to
that image.
2. An ImageThumbnails table representing the relationship of one image
being a thumbnail of another image. Note that this table would allow
multiple thumbnail images associated with a given larger image. It will
also represent the case where image I2 is a thumbnail of image I1 and
image I3 is a thumbnail of I2, that is, I2 is smaller than I1 and I3 is smaller
than I2.
3. An ImageCategories table to represent groups of images, e.g., images I1,
I2, and I3 are part of the Autumn group. Obviously, an image can be in
more than one group.

For example, to get all full and thumbnail image pairs for the Autumn
group, one would query

SELECT IC.image_catego ry_name,
IT.full_image_n ame,
IT.thumbnail_im age_name
FROM ImageCategories AS IC
INNER JOIN
ImageThumbnails AS IT
ON IC.image_catego ry_name = 'Autumn' AND
IC.thumbnail_im age_name = IT.thumbnail_im age_name

This, intuitively, seems to model the problem directly and naturally.

Regards,
jag

[Summasummarum]

Re: Suggestions for a small database layout...Very simple

John Gilson wrote:[color=blue]
> SELECT IC.image_catego ry_name,
> IT.full_image_n ame,
> IT.thumbnail_im age_name
> FROM ImageCategories AS IC
> INNER JOIN
> ImageThumbnails AS IT
> ON IC.image_catego ry_name = 'Autumn' AND
> IC.thumbnail_im age_name = IT.thumbnail_im age_name
>
> This, intuitively, seems to model the problem directly and naturally.[/color]

Yes...you are right. It seems more intuitive..I'll have a go at it monday.
Thanks for your thoughts..

/Summa


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27-11-2003