SQL table, key, field design

i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything similar before.

to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty.  when a site browser clicks a section, it will bring up a gridview of the "key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.)  past and current customers will create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.

as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server.  i have code to do either.  i am trying to construct a database design to handle all issues.  i like the idea of storing photos in the database more, just not sure how to architect it.  it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing job photos.

i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all photos to the same job.  the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty.  does this method seem feasible? if this system seems workable, i can throw in a boolean default to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis.  but i am a little stumped how he can maybe order them?  maybe an int field that he can edit and just manually put the jobs in the order he wants? and then put the photos under the jobs in the order he wants as well?

it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know

any ideas, suggestions, and help is greatly appreciated.

Read More

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s