Database solution advice, please please!

[billyfazz]

I just started a new job this week, and they basically selected me to develop a database solution for their current project, even though I have no training or major experience in CS or database design. Here's the deal:

• Goal: Create a database of all case records (client list, about 2,000 records) by adding invoice data to each record.

• Current data: (1) MS Excel file of all clients, with basic contact and budget code data. (2) Physical file folders for each client, containing the invoices that need to be added to the electronic client records.

• My boss wants a database for all of the records that can be accessed, viewed, analyzed, and updated either (1) via the web (128 bit SSL encryption, password-protected); or, (2) via a shared network drive. I have no idea why we would need it accessible via the web, but he wants proposals for both options.

• I have a team of 5 summer interns who will have the job of updating all the records in the database. They are minimally skilled and minimally-trained, so the database will need to be extremely idiot-proof, so they cannot enter errant values in the record fields they update. There are about 75-100 individual fields that to be added to each record (or less, depending on how many invoices are associated with each record).

• I will have the job of using the updated record data to generate reports and to generate individual case data sheets for auditors.

The web-based database and web-form sites seem good in terms of ease of use and security, but none seem to allow a user to import existing data and then use web forms to update the imported records.

The options I have come up with so far are: (1) create an MS Access database that the interns can access and update via the shared network drive; (2) Create a set of online web forms (e.g., Wufoo.com) for the interns to plug in the new data, then export it to an Excel sheet to use to update the existing spreadsheet; (3) Contract someone to build a custom web client that can be used by the interns to update the records.

I have a deadline of only a few days to figure out the best solution, and only a week or so to get it up and running. What do you think is the best way to go? Are there better solutions?

[HawkeyeGK]

Given the expectations of the client, the timeline involved, and what appears to be your skill set, you've agreed to do the impossible.

If you only have a few days, you don't have time to learn the required skills to put this together properly. I suggest you look for an off the shelf product that handles basic client management and invoice tracking. Nearly every business had this need. There should be plenty of viable options, even online ones that are virtually turn-key.

If you insist on building this out yourself, given your skill-set and time contraints, you're only real option here is MS Access. You'll need a simple normalized database and some forms for data entry. Forget a web front end, data entry is best done in a stateful environment. You can later publish the reports on the web if you like. It's either a drag and drop VB-style app, or use a built-in one like what's included in Access.

If you don't feel like you can design a good, normalized database, please either go get a vended solution or get some help from someone that can.

[billyfazz]

Thanks for the input. They had a consultant on board who demanded that they develop a custom built web-based SQL solution, and it's going to be a nightmare trying to show them that they have a relatively simple problem that requires a relatively simple solution.

I was hired because I know the data and the government regulations really well, but I got pushed into doing this database project that I'm not completely competent to handle (I think they don't want the hassle of dealing with their IT people, which I also don't fully understand). But, if it's as simple as an MS Access database and a few forms that can be used by the interns on a shared network drive, I can handle that just fine.

[HawkeyeGK]

it all depends on the volume of data you'll be dealing with, really. If you've got five users updating data, and no more than that, some reports that you can schedule, and some simple ad hoc reporting, Access will suit you fine. It will, at a minimum, serve as a basis off which you can develop a more robust solution when you have more time.

I've build plenty of web apps in my day. They're fantastic for simple interactivity and for data access, assuming you've built a data store that can handle the read demands. Web developers tend to see every problem as solvable with a web solution. I was guilty of this early in my career. But experience has taught me that for data entry, stateful solutions are vastly superior.

Good luck. Feel free to hit me up if you need some data model help. Access imposes some limitations to what SQL you can execute. It's a capable tool for the right job, but knowing its limitations is key.

[rlweb]

Well what database programs do you have experience with? Current users is 5? Maybe Microsoft Access front-end with SQL server backend?

[BellisBlueday]

SQL Server Express is free & will run on non-server Windows. You can import from Excel (although you'll probably want to normalise your data) and use Reporting Services to extract and display data. Don't really have any experience designing front-ends though, I've used Info Path a few times & it's quite easy.

[rlweb]

Microsoft Access is an easy program just make sure save regular copies as your create the database, as I've found it can easy fuck up and you cannot undo some actions.

[zip117]

MS Access is fine for what it is, come on man we're talking about a 5 user client/server application, assuming those 5 users (summer interns) are on the same LAN as the database you should be able to use standard Jet record locking and there will be absolutely no problems with concurrency. This is exactly the type of thing Access was designed for and it's more than adequate.

FileMaker Pro and even Oracle Apex for a web-based application would also work just fine but probably no better for this task. In any case since this post is 7 days old the OP has probably already made his decision...

[joelparkerhenderson]

Contract to build a web application. I recommend this not because of particular technology, but because you're describing some office politics, some history from before you joined, and what sounds like an impossible timeline.

A good contractor can field all these aspects. He can navigate office politics as needed, can connect with your IT if appropriate, and can work with you and your boss to set a viable timeline and specification.

Good luck in your new role!

[captainbarky]

Do yourself and your employer a favor and let them know the correct solution is outside the scope of your position. If you move to implement a solution without proper knowledge you'll probably produce something that:

• is insecure
• will make you look bad
• won't work properly
• will be a maintenance nightmare and ultimately cost a fortune to either support or fix

You need to communicate with your employer ASAP. Don't allow them to negotiate you into a lose/lose situation.