Maintaining Customer Data

Question

All our applications rely on a certain amount of client data i.e. Lookups for comboboxes, users, roles, user-roles...

We currently use a spreadsheet to map the data and generate insert scripts that are then imported into SQL server. The has seemed to work for us but it is very difficult to update and maintain when there are a lot of changes going on (excel is not good at merging data) and it is slow when there is a lot of data.

I am sure this is a common problem and would to hear how others have approached it so we could borrow some ideas and improve our process.

Answer 1

You will probably have the same issue with any disconnected file based approach. My suggestion would be to build a web page with your lookups that will change and give your customer access to it. Let them maintain the data.

Answer 2

Excel rocks for importing data. You can quickly edit, troubleshoot, and it has lots of options to organize data.

Several developers at my company tried to phase out the Excel data import sheets, but they all failed (one after 6 man-months.) It not always worth the effort to build a specialized data entry web site or windows app. :)

Answer 3

Infopath and SharePoint? (just throwing it out there. It takes some skill to setup well which I would generally doubt that you have at your company)

Answer 4

I suggest SharePoint as well. Specifically, Windows SharePoint Services 3.0. It's pretty simple to get it up and running and it's included with Windows Server. You can easily create a custom list (you can even import it from Excel) and best of all, you can link it to Outlook and do mail merges on it. Great stuff.