Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2005-09-11 : 11:56:59
|
I am trying to make an image gallery on my website and have a table in my DB that holds all the info on each image in it. I think I may have created it wrong though because I am running into a problem that I can not figure out an easy way to solve. Here is the SQL code for my table:CREATE TABLE [images] ( [Game] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [SubSet] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Image] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Height] [numeric](3, 0) NOT NULL , [Width] [numeric](3, 0) NOT NULL , [AltText] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO Now to explain the a few of the colums to make sure your on the same thinking as I am.Game: The name of the game the image is fromSubset: What catagory the image falls into (I.E. Misc images, Group Images, Landscape Images, ect)Image: This is the full name of the image, including the extention.The problem I am runing into is when displaying a single image I want to have buttons at the bottem of the page that lets you move to the last/next image in the gallery. The problem is that I can not figure out a way to figure if there is a last/next image in the subset and what that image is. I was thinking about adding a couting colum so each image would have a digit like "1" "2" ect assoicated with it but then I still do not know how to find out if the image is the last one in subset or not.I hope this makes sense and you can understand what I am trying to do. If you have any suggestions on how to recreate or modify the table (or move to more then one table) I would like to hear them.-- If I get used to enving others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-11 : 12:42:08
|
| Wouldn't you want to have a number for each Game/Subset combination? I would assume each of these combinations represents some type of gallery. If you assign a number for each, you just have to move from min to max. Another thing you could do is pull each set into a table variable with an Identity field when they select a certain group of images to look at. If you have pages defined as a certain number of images, you just need to filter the table by given range each page view and navigation event.It really depends on what you are doing with this data to determine if it's right or wrong. What is the natural key of the table though??? You should have a primary key, or at least a unique constraint, to enforce the natural key and make sure the table is a good long-term solution.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2005-09-11 : 13:01:26
|
quote:
Originally posted by derrickleggett
Wouldn't you want to have a number for each Game/Subset combination? I would assume each of these combinations represents some type of gallery. If you assign a number for each, you just have to move from min to max.
That is what I was thinking about doing, giving each game/subset image a uniqe number, and you are right in assumeing that each game/subset combo represents a uniqie gallery. But my problem with this is I do not know how to pull the max number so the script i use add new images with to assign the right numbers for new images. And I am in the same boat when displaying a single image for the gallery on knowing if it is the last image in the set or not. quote:
Originally posted by derrickleggett
Another thing you could do is pull each set into a table variable with an Identity field when they select a certain group of images to look at. If you have pages defined as a certain number of images, you just need to filter the table by given range each page view and navigation event.
I am sorry but I do not understand what your at trying to say here.quote:
Originally posted by derrickleggett
It really depends on what you are doing with this data to determine if it's right or wrong. What is the natural key of the table though??? You should have a primary key, or at least a unique constraint, to enforce the natural key and make sure the table is a good long-term solution.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
When creating the table I found no good table to make a primary key of, I can not have it of the image names because they will duplicate them selfs in some situations. I assumed the combination of game name, subset name, and image name would be unique enough. I do know what a primary key is but I have not heard of a natual key.-- If I get used to enving others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-11 : 21:58:56
|
| If you insert each image one by one, then getting the next number is fairly easy. You would need a unique constraint on the game/subset/image number. Then, on the insert statement, just insert MAX(image_number) + 1. You might encounter some contention here. On extremely high transaction systems, I've created a single table for each number with an identity column. The table just acts as to give the next numbers for each combination. It's a "increment object" basically.If you SELECT game, subset, image FROM table WHERE game = 'x' AND subset = 'y' into a table variable declared as such:[code]DECLARE @image_collection TABLE(ident INT IDENTITY(1,1) PRIMARY KEY, game varchar(15), subset varchar(15), image varchar(50)) The ident will give you a recordset you can "page" through. The natural key is the unique identifier for the entity, based on the data being stored as part of the identity. What naturally defines each record as being that entity? In this case, it probably is the game, subset, and image.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
I think I screwed this table up, did I?