SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 File path format and data type
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cesark
Posting Yak Master

Spain
215 Posts

Posted - 07/20/2004 :  06:42:31  Show Profile  Reply with Quote
Hi,

I want to put some images file path to a table column. What kind of data type I have to choose? Text, varchar,..

And, What kind of file path format I have to use? Maybe this:

C:Inetpub/wwwroot/images/certificates/image1.gif
C:Inetpub/wwwroot/images/certificates/image2.gif
...


Thank you,
Cesar

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 07/20/2004 :  12:25:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
It just depends on how you've designed your system. You might only store part of the path as the first part might be the same for all of them. I would use varchar data type with a length of 256.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/20/2004 :  13:54:29  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Also, I wouldn't use a mapped drive. I would use this format so it's more portable.

\\server\share\file.gif

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/20/2004 :  14:11:51  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
don't forget normalization!

if they are all in the same folder, store the root folder in 1 table somewhere (like a "Preferences" table or a "Settings" table), and store only what makes each filename unique in your filenames table. this will keep it portable (you can switch roots easily, including to/from UNC and drive letter mapping) and you will save space.

- Jeff
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 07/20/2004 :  15:09:11  Show Profile  Visit MichaelP's Homepage  Reply with Quote
Jeff has it right.
I usually create a "system params" table someplace that stores the root directory's for all of these sort of things, and then I have the relative path to the images in their respective tables.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

cesark
Posting Yak Master

Spain
215 Posts

Posted - 07/20/2004 :  18:59:35  Show Profile  Reply with Quote
I don' t know if I have understood it well, but I have decided the follow:

The thing that makes the image files unique are the 'offer_id', because every offer has its images. So, the 'Offers' table will hold the image file name in some column: offer_id_image1.gif

And I will have only one directory 'C:Inetpub/wwwroot/images/certificates'

I don' t understand what is 'a table someplace that stores the root directory's', How can I do this and which is its intention?

Thank you,
Cesar
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/20/2004 :  19:51:14  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
The intention is to plan for the future. If you change the location of your files someday or migrate to a new system, or even sale the program, you can dynamically set the path. Then if you change the location, it's no big deal. You just change the path in that one place and you're done. Otherwise, you would actually have to update each and every file pointer.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

cesark
Posting Yak Master

Spain
215 Posts

Posted - 07/21/2004 :  04:13:21  Show Profile  Reply with Quote
No, the part that I don' t understand is the Jeff comment:

quote:
..if they are all in the same folder, store the root folder in 1 table somewhere (like a "Preferences" table or a "Settings" table),..


Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/21/2004 :  07:58:00  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
His comment is just saying that you store this: '\\server\share\directory\' in one table, and this 'file.jpg' in another table with a relationship to the other table.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

cesark
Posting Yak Master

Spain
215 Posts

Posted - 07/21/2004 :  11:20:14  Show Profile  Reply with Quote
Ok I see, then for example, in the 'Preferences' table to store the path_id in one column, and the complete path in another column. Then, in the table 'Certificates', or whatever I need, to store the file name in one column (image36.gif), and the path_num (related with path_id from the 'Preferences' table) in antoher column. Isn' t it?

Sorry that I insist, but only one more question about the path format: I store the images in the same folder that the ASP.NET VB.NET web application is, and I saw that it is enough to write 'images/certificates_A/image25.gif' in the database table to be able to access to the image. Is this path format correct? In your examples, What '\\server\share\directory\' does? Is it necessary to specify the server name? What is '\share\'?

Thank you
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/21/2004 :  11:52:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
No...

are all the images in the same folder? If so, then the root of this images is a CONSTANT value -- you can hard-code it into your application, or make it something that you can change easily. The second option is more flexible. So where can you store something like this? In a table! Just create a table, with 1 row in it, and store the base path of all images in 1 column. You can lookup the root anytime you need by querying this table, and you can configure or change this root quite easily.

Then just store the filenames (w/o the full path) in your images table.

No need to relate them, no need for any kind of ID column to be generated. You are just storing 1 value in 1 column in 1 table, like you'd store a constant or a setting for an application.

- Jeff

Edited by - jsmith8858 on 07/21/2004 12:53:01
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/21/2004 :  12:57:43  Show Profile  Reply with Quote
Yes...

I object to the design where you store one "global" path in a settings table.
This means that You can never have more than 1 folder where the images are, this is not planning for the future.
(Ok, you could set up a M:M relatinship between path(s) and image(s), or start relating them)

In such a case I would prefer relating the paths with the images the way cesark proposed.
Furthermore here I would really consider using the path as "natural key", and not using the Id approach proposed by cesark.

-- Example
SET NOCOUNT ON

CREATE TABLE Folders( path varchar(256) not null primary key clustered )
CREATE TABLE Files( path varchar(256) not null REFERENCES Folders(path) ON UPDATE CASCADE, filename varchar(256) not null, PRIMARY KEY CLUSTERED( path, filename ) )

insert Folders(path) values('C:Inetpub/wwwroot/images/certificates/')
insert Files(path,filename) values('C:Inetpub/wwwroot/images/certificates/','image1.gif')
insert Files(path,filename) values('C:Inetpub/wwwroot/images/certificates/','image2.gif')

select path + filename from Files

PRINT 'Make it portable and map instead to a server share:'
update Folders set path = '\\SERVER\SHARE\' where path = 'C:Inetpub/wwwroot/images/certificates/'

select path + filename from Files

DROP TABLE Files
DROP TABLE Folders

/rockmoose
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000