Images Table

2006-07-20 Thread Nicholas Vettese 
I am looking to create a database that holds the location of images on my 
server.  I was wondering if anyone could help me understand if I am doing it 
correctly, and if I either need to add, change or remove something from the 
current table code.

img_idINTNOT NULL auto_increment PRIMARY KEY,
user_id   INTNOT NULL,
location  VARCHAR(255) NOT NULL,
imgtype  VARCHAR(20) NOT NULL,
img_tag  VARCHAR(50) NOT NULL,
img_rate VARCHAR(20) NOT NULL,
img_commentsVARCHAR(50) NOT NULL,
KEY (img_id);

Thanks for any help.

nick

Re: Images Table

2006-07-20 Thread Chris White 
On Thursday 20 July 2006 07:19 am, Nicholas Vettese wrote:
 img_idINTNOT NULL auto_increment PRIMARY KEY,
 user_id   INTNOT NULL,
 location  VARCHAR(255) NOT NULL,
 imgtype  VARCHAR(20) NOT NULL,
 img_tag  VARCHAR(50) NOT NULL,
 img_rate VARCHAR(20) NOT NULL,

good

 img_commentsVARCHAR(50) NOT NULL,

bzzt, bad!  Note here comment(s).  You have a 1 to many relationship, as one 
image can have many comments.  What you need to do is have a separate table 
for that:

CREATE TABLE image_comments (
  id SERIAL PRIMARY KEY,
  comment TEXT NOT NULL,
  image_id INT NOT NULL,
  CONSTRAINT `foreign_images_comments`
  FOREIGN KEY `foreign_images_comments` (`image_id`)
  REFERENCES `[image-table]` `img_id`
  ON DELETE CASCADE
);

then:

SELECT comment FROM image_comments WHERE image_id = [image_id_here];

Why?  Think about how you'd need to add comments.  You'd have 2 choices:

1) Shove all the comments in one column (get ready for a HUGE table)
2) Create a separate image row for each comment (lots of duplicate data, bad!)

 KEY (img_id);

Not needed, you already declared it a key above (PRIMARY KEY)

 Thanks for any help.

 nick

-- 
Chris White
PHP Programmer/DBoo
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]