Re: Categories like wordpress

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ryan S wrote:
> Hey,
> Am not really used to using the JOIN in SQL so am a bit confused as to what kind of data I need to enter into this table:
> 
> image_category_mapping table:
> - image_id
> - category_id

It comes down to database normalization
(http://en.wikipedia.org/wiki/Database_normalization).

The idea behind database normalization is you only ever have one copy of
the data, you don't have to update multiple tables to reflect a change.

So you only store the image in the 'images' table:

create table images
(
  image_id int auto_increment primary key,
  image_name text,
......
);

For multiple categories to share a particular image, there needs to be a
link between a category and an image.

If you only ever want one image per category, you could put the image_id
in the categories table:

create table categories
(
  category_id int auto_increment primary key,
  category_name text,
  image_id int,
.....
);

However the problem with that is if you want more than one image,
instead of adding another field to the categories table:

create table categories
(
  category_id int auto_increment primary key,
  category_name text,
  image_id int,
  image_id_two int,
.....
);

which means if you want a category to have 3 images, you need another
field and so on, so instead you create a separate table:

create table category_images
(
  category_id int,
  image_id int
);

which means you could have 10 images for that category and no db changes
are necessary.


So you create the image as normal (insert into images ...).

Then when you link it to a particular category, you insert into the
separate table:

insert into category_images (category_id, image_id) values (.....);


This also means if you update the image (eg change the name or alt-value
or whatever), it's automatically reflected for all categories.

How do you get an image for the particular category?

SELECT i.* from images i INNER JOIN category_images ci ON (image_id)
WHERE category_id='X';

That will give you (multiple if necessary) images for that category.

-- 
Postgresql & php tutorials
http://www.designmagick.com/

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux