SQL Query erm...query

Shicky · 22 Apr 2011 at 16:37

So im thinking of how to structure my database by considering all the forms I will want users to interact with. In this particular case I want users to be able to upload images which will go in a jquery slideshow on the main page. Problem being I will want an images table and a promotions table to handle this.

Now for the images table I would want this to contain - ImageID, Title, Source, Datecreated, Dateupdated. Then for Promotions table I would have it contain - PromoID, linkaddress, ImageID.

Now this will have a one to one relationship of course. The form for changing the promo images would then have a preview of the current images being used (this would be limited to 5 only) and the information stored about them. Each image would have a remove button which I would want to delete the image/promo from the database. They would then be able to add a new image/promo in its place, essentially just overwriting the information in the database for that item. (Horrible I know but best way I can think of) - No change would occur in the DB until the user hit the save button at the bottom.

My question then is basically is it easy to achieve the above? For example how do I edit the information from both tables at the same time?

Space is apparently a premium so I dont want unused images sitting on the server

*Sorry mixed it up - retyped it to make more sense now

Ciphon · 22 Apr 2011 at 17:49

So you want to have a maximum of 5 records in each table at any one time?

So a user would create record 1, 2, 3, 4, 5. Then the user decides they want to do away with record 3, and replace it with another. This would mean the Title and Source columns would be updated in the Images table, and LinkAddress in the Promotions table - as there would be no need to update anything else.

It would be easy enough to write a stored procedure to do something like this - so long as I'm understanding what you're asking for?

It does raise a few other questions though... What if a user doesn't want 5 promotions? Or they had 5, and now want to remove 1 and not replace it with another... You'd need an "IsActive" flag to get around this if you don't want to delete records.

Sida · 22 Apr 2011 at 18:02

Editing both tables at the same time isn't a problem, but I'm wondering why you want to edit current records instead of removing them? You could simply split it into two functions. One that will remove a record from both tables (promotion table would have to be modified first to avoid issues with that foreign key), and another that will add to the tables assuming the number of rows is under 5.

Another alternative is to keep a history of images and promotions but only have 5 active. All that would mean is adding some sort of IsActive field like Ciphon suggested and only allowing 5 rows to be active. The user could then have as many rows in the "history" as they want (or a history limit set by you or whatever) but only 5 can be active at one time.

I know you didn't ask for the second thing but it's just an idea.

Shicky · 22 Apr 2011 at 18:20

Yes there will be five records only in terms of promotions. On the form I will handle this using Javascript (have a small add image link if 5 images arent already present.)

In terms of why the overwriting instead of a new record its because I don't want a history of old stuff sitting on the server as apparently space is an issue, i thought doing this may be easier. I suppose deleting is still an option and probably a better one....I seem to forget my justification for the overwriting method, i thought there may be issues ensuring only 5 records were possible.

Shicky · 22 Apr 2011 at 18:21

Ciphon said:
It does raise a few other questions though... What if a user doesn't want 5 promotions? Or they had 5, and now want to remove 1 and not replace it with another... You'd need an "IsActive" flag to get around this if you don't want to delete records.

Seems deleting may be the way to go then!

Sida · 22 Apr 2011 at 18:33

Shicky said:
Yes there will be five records only in terms of promotions. On the form I will handle this using Javascript (have a small add image link if 5 images arent already present.)

In terms of why the overwriting instead of a new record its because I don't want a history of old stuff sitting on the server as apparently space is an issue, i thought doing this may be easier. I suppose deleting is still an option and probably a better one....I seem to forget my justification for the overwriting method, i thought there may be issues ensuring only 5 records were possible.

If space is an issue then yeah you should probably forget the history. The actual records shouldn't take too much space (depending on the number of user you have ofcourse, but it would have to be quite a lot) so I'm assuming you plan to remove the images once they're not in use?

If this is the case you also have to consider the "save" button. I'm guessing the user will be allowed to upload an image and see how it looks etc before pressing the save button. If they decide not to save, you'll have to go and remove any images they uploaded but you won't be able to do this via the database as there will be no record.

Deleting the records shouldn't be an issue really. And for checking only 5 records are possible, you can just do a row count on a select * query.