niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » MySQL
Images Table
Post new topic   Reply to topic Page 1 of 1 [2 Posts] View previous topic :: View next topic
Author Message
Nicholas Vettese
*nix forums beginner


Joined: 22 Jun 2006
Posts: 6

PostPosted: Thu Jul 20, 2006 2:19 pm    Post subject: Images Table Reply with quote
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_id INT NOT NULL auto_increment PRIMARY KEY,
user_id INT NOT NULL,
location VARCHAR(255) NOT NULL,
imgtype VARCHAR(20) NOT NULL,
img_tag VARCHAR(50) NOT NULL,
img_rate VARCHAR(20) NOT NULL,
img_comments VARCHAR(50) NOT NULL,
KEY (img_id);

Thanks for any help.

nick
Back to top
Chris White
*nix forums beginner


Joined: 21 Apr 2006
Posts: 43

PostPosted: Thu Jul 20, 2006 3:03 pm    Post subject: Re: Images Table Reply with quote
On Thursday 20 July 2006 07:19 am, Nicholas Vettese wrote:
Quote:
img_id INT NOT NULL auto_increment PRIMARY KEY,
user_id INT NOT NULL,
location VARCHAR(255) NOT NULL,
imgtype VARCHAR(20) NOT NULL,
img_tag VARCHAR(50) NOT NULL,
img_rate VARCHAR(20) NOT NULL,

good

Quote:
img_comments VARCHAR(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!)

Quote:
KEY (img_id);

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

Quote:
Thanks for any help.

nick

--
Chris White
PHP Programmer/DBoooooo
Interfuel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [2 Posts] View previous topic :: View next topic
The time now is Mon Dec 01, 2008 6:45 pm | All times are GMT
navigation Forum index » Databases » MySQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts How to ALTER a table using the ALTOBJ procedure Serge Rielau IBM DB2 1 Fri Jul 21, 2006 1:06 pm
No new posts Can't Select External Table from CSV File Resant Server 1 Fri Jul 21, 2006 2:45 am
No new posts wxPython: wxStaticBitmap and large images Roger Miller python 1 Wed Jul 19, 2006 11:27 pm
No new posts grant on a specified table to a user Shinyday Server 4 Wed Jul 19, 2006 6:03 am
No new posts Database or table level logging? David Felio MySQL 5 Tue Jul 18, 2006 6:57 pm

Cheapest mobile phones | Loan | Mortgages | Loans | Credit Card Debt Consolidation
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1389s ][ Queries: 16 (0.0681s) ][ GZIP on - Debug on ]