Hi everyone,
I'm kinda new at this database design stuff and I have a question.
I'm trying to create a database to store images that are submitted by a user. I've figured out pretty much everything but how to store details about the images.
There will be an uncertain amount of details about each image, but they will only be selectable from a list of available details. In other words, Image A has detail 01, detail 02, detail 03, etc.
Should I keep track of these details in a seperate table or should I keep track of them in the images table? When the user submits an image, they will select 'add detail' and then select a detail. The detail will list itself on the submission page and then the user may select 'add detail' again and again. I have no idea how many details they will select... but I need to keep track of each one.
Should I just limit the number of details they can submit on each photo?what database system are you using?
from how you describe what you want to do I would probably store them in a seperate table such as(all examples are php/mysql related):
CREATE TABLE `details` (
`id` INT( 255 ) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`content` TEXT NOT NULL ,
`imgid` INT( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
);
id = primary key to increment the index of the table
name = the name of the detail
content = the value of the detail
imgid = the id of the image the detail is for
and then do something like:
//showdetails.php
$imgid = $_GET["id"]; //id of the image you want details for obtained from the query string ie: showdetails.php?id=image_id
mysql_connect('localhost','username','password');
mysql_select_db('dbname');
$query = mysql_query("SELECT * FROM details WHERE imgid='$imgid' ORDER BY name");
while($row = mysql_fetch_array($query))
{
echo '<b>'.$row["name"].'</b>:'.$row["content"].'<br>';
}
also....I would not recommend storing the image itself in the db...I would recommend that you store the url to the image and have the image upload to the directory.As if you store the image it would be a very bogged down table if you had the image in the db.
Hopefully this helps...the examples are php/mysql but I'm sure you could adapt them/understand what I'm refering to.Thanks so much. I'll build a seperate table to keep track of the details. You also answered another major question I was facing - where to store the images. Blobs are more secure - but you're right, I guess there would be so many images in there that it would slow everything else down. Thanks again!
I'm kinda new at this database design stuff and I have a question.
I'm trying to create a database to store images that are submitted by a user. I've figured out pretty much everything but how to store details about the images.
There will be an uncertain amount of details about each image, but they will only be selectable from a list of available details. In other words, Image A has detail 01, detail 02, detail 03, etc.
Should I keep track of these details in a seperate table or should I keep track of them in the images table? When the user submits an image, they will select 'add detail' and then select a detail. The detail will list itself on the submission page and then the user may select 'add detail' again and again. I have no idea how many details they will select... but I need to keep track of each one.
Should I just limit the number of details they can submit on each photo?what database system are you using?
from how you describe what you want to do I would probably store them in a seperate table such as(all examples are php/mysql related):
CREATE TABLE `details` (
`id` INT( 255 ) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`content` TEXT NOT NULL ,
`imgid` INT( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
);
id = primary key to increment the index of the table
name = the name of the detail
content = the value of the detail
imgid = the id of the image the detail is for
and then do something like:
//showdetails.php
$imgid = $_GET["id"]; //id of the image you want details for obtained from the query string ie: showdetails.php?id=image_id
mysql_connect('localhost','username','password');
mysql_select_db('dbname');
$query = mysql_query("SELECT * FROM details WHERE imgid='$imgid' ORDER BY name");
while($row = mysql_fetch_array($query))
{
echo '<b>'.$row["name"].'</b>:'.$row["content"].'<br>';
}
also....I would not recommend storing the image itself in the db...I would recommend that you store the url to the image and have the image upload to the directory.As if you store the image it would be a very bogged down table if you had the image in the db.
Hopefully this helps...the examples are php/mysql but I'm sure you could adapt them/understand what I'm refering to.Thanks so much. I'll build a seperate table to keep track of the details. You also answered another major question I was facing - where to store the images. Blobs are more secure - but you're right, I guess there would be so many images in there that it would slow everything else down. Thanks again!