File path format and data type

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

File path format and data type

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2004-07-20 : 06:42:31

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

38200 Posts

Posted - 2004-07-20 : 12:25:32

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-20 : 13:54:29

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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-20 : 14:11:51

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

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-20 : 15:09:11

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>

cesark
Posting Yak Master

215 Posts

Posted - 2004-07-20 : 18:59:35

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-20 : 19:51:14

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.

cesark
Posting Yak Master

215 Posts

Posted - 2004-07-21 : 04:13:21

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),..

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 07:58:00

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.

cesark
Posting Yak Master

215 Posts

Posted - 2004-07-21 : 11:20:14

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-21 : 11:52:41

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-21 : 12:57:43

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources