SQL Query erm...query

[Shicky]

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

 

[Shicky]

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]

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!