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 |
|
CactusJuice
Starting Member
46 Posts |
 Posted - 2002-10-31 : 16:53:04
|
| I would appreciate any suggestions or tips please. As an alumni, I am designing a site for my high school. Alumni will be able to upload multiple photos. And this is where I have a question.Specifically, I have looked around the web. Some sites that have this one to many relationship (1 record, many photos) seem to be doing this...(assuming a record ID# 12345)...they show related photo file name like 12345A.jpg, 12345B.jpg, 12245C.jpg. Etc.On SQLTeam.com I found a couple cool threads here. One re the IDENT_CURRENT and a good one about using CONVERT. But before getting into programming I'm having trouble thinking through the best architecture for this table.My question. Is this the best way to do the PhotoFileName? Should this be the PK or do I need another integer type PK? How best to incremtn the trailing letter. I'm not sure how to build the table that stores the photo names (files will go on hard drive). Obviously it needs the StudentID.tblPhotos---------PhotoID int PKStudentID int FKPhotoFileName varchar(10) ?Description varchar(100)thanks,Cameron |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-31 : 17:12:42
|
| Here's one way to tackle this1. Make PhotoID a GUID (unique identifier). 2. Store the extension of the file in the DB (JPG, GIF, etc)2. Return the GUID out of your Stored Proc as an output param.3. Once you have the GUID, save the file as GUID.FileExtensionThat way, you don't run into file name collisions, and you don't have to worry about this "incrimenting" issue at all.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 17:15:22
|
| Actually, IMHO a better approach would be to have everyone register as a user (you're already doing that) and create a disk directory/folder for each user/student ID. Then you can name the files anything you want, including the original name of the file the person uploads, and upload it to their folder. You could even get away with not storing the file names in the table at all, technically speaking, because you wouldn't need to differentiate it from someone else's files. You can still do it for the sake of putting a description on it, obviously. In any case you don't need to worry about incrementing letters or anything like that. |
|
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2002-10-31 : 17:24:19
|
| Yes, I everyone must register/login. And I should have said that this is not just for my graduating year. I want it to be scalable...mey high school was founded in the early 70's. I am guessing there's been at least 15-20K students go through halls so far. Eventually I want to import all of the students. The web server I'm using is just a shared server account...since I'm doing this as a gift--on my own dime :) So I don't think it would be feasible to give everyone theri own directory. Nice idea though for smaller groups. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 17:47:15
|
| Well, consider this: you don't need to create a folder for a user unless they actually upload something. You'd end up with the same number of files anyway, and none of them have to be renamed using the GUID. That way the students can view a list of the stuff they have uploaded already and match it back to the original files they have, and they're less likely to upload the same file multiple times; the GUID-as-file-name will make this much harder to enforce. As far as the file system is concerned, there's no real difference between a file and a folder...a folder is just a special kind of file, and it doesn't take up any space (well, a little bit, only to hold the name, size, and date of the files in the folder) Unless there's a quota on how many folders you can have it won't cost you any more or be more burdensome than having thousands of GUID files in one folder. You can even create folders for each graduating year and subdivide the students that way, it might help get around any quota limits. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-31 : 18:07:34
|
| If you want scalability then the directories is definitely the way to go on storing the images, whether you have the data of location and relation to students in a database or not. The directories will take very small amount of space compared to your 20,000+ pictures. Also if you name the directories something uniquely related to the student you can access that directory very quickly instead of having to search through thousands of files (ouch..), and like rob said the users can then manage their own pictures. |
|
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2002-10-31 : 18:52:23
|
| OK thank you all. Good info about the directories. I never would have guessed. Just assumed having thousands of them would somehow be overkill. I also like MichaelP's suggestion. It will work if for some reason my web host has a problem with the numerous directories. Thanks. |
|
|
|
|
|
|
|
|
Best way to proceed?