Architect - Design Import question

Last Post 04 Oct 2006 02:51 AM by mwesch. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Superman_SQL
New Member
New Member

--
04 Oct 2006 12:40 AM
A vendor will send me either CSV text data or a CSV file through our web service. In my web service, I need to save the CSV text data or CSV file to a database table. If it is CSV text data, then the field data type will be Text but if it is a CSV file, I will blob it into an Image field. Agree so far?

Now, when a user is ready to use this file or data, they will click on it and it needs to be parsed out to another table.

If it is in a file, I will probably blob it out to a network directory and use a DTS package to dump it back into the second table.

If it is in text data format, I would have to create a parser in a SP, right?

Please give me some ideas what is the best way you would take and which route you would take between the two above?

I am looking for the Best Practice and at the same time, least work and then least future maintenance. It needs to be dynamically enough so that it can take additional fields in the future.

Thank You
mwesch
New Member
New Member

--
04 Oct 2006 02:51 AM
Clark,

Not sure of all your requirements.

First question I would ask is there a need to store the original file, and does that file need to be stored in the database. If so, then your first point about text/image blobs is okay. I might however use only an image type to keep my data model clean, and have another field in the table that identifies the file type stored in the image:

DataFile
--------------------
VendorID int,
Date datetime,
FileType varchar(10), --(txt, xls)
File image --(actual file)

If the files don't need to be stored in the database, then you might create a file system directory to store the files and maintain just a pointer in your database:

DataFile
--------------------
VendorID int,
Date datetime,
FileType varchar(10), --(txt, xls)
File varchar(250) --(path/filename to file system object)

The difference between 2 methods above is that you get to avoid the complexities in loading blob data into and out of the database (supposed to be easier in SQL2005, but I haven't tried it). The downside is that some of your data is outside the database.

Second question is there a need to save source files at all? It might make sense to process the files and load them into a table as your web service is pulling them from the vendor. This might not be feasible if the data files are not clean or consistent and require user intervention to load/process.

Superman_SQL
New Member
New Member

--
04 Oct 2006 09:27 AM
Definitely yes, the file needs to be stored and I know, as a DBA, it is not good to blob. However, that is one of the project requirements.

Sorry, if I sound confusing in the first post but I actually don’t really need the file type because I am choosing either text or image data type. Both are in CSV format.

I basically just want that when the user is ready, there will be an action to go against the first table and grab either the text data or the blob and dump into the second table, which will be parsed out from the CSV format. Hope you understand what I am trying to say but the first table contains the original for historical purpose. The second table resembles the actual CSV format.


Acceptable Use Policy
---