Company database question, please help

Hello,

I'm currently working for an Wholesale Electronic Distributor company and we're looking to upgrade our entire in-house database software such as Accounting, Inventory, Sales, Purchasing, etc..

We're currently using a DOS based system from dBase III Plus. I've searched the web and haven't found anything worth using that would fit our needs, or within our budget range. So I've been thinking and was wondering if MySQL would work.

Now what I would like to know is would it be good idea to run a local in-house server connected on a Peer-to-Peer network w/ Windows XP Pro as our OS, PHP and MySQL and have the employee's use the database system thru Internet Explorer for our Accounting, Inventory, Sales and Purchasing?

If this is a bad idea, can anyone suggest another method?

Thanks,
Dan

Well... yes, but you are opening up a can of worms. MySQL is better than DBASEIII, but really as long as you are upgrading to a modern database system, why not try one that supports full SQL operations? (PostgreSQL, Interbase/Firebird, SAPDB are the open-source choices; SQL Server, SyBase, etc... are the commercial ones) MySQL is still quite limited when it comes to serious business logic. (limited transactions and foreign key constraints, no views, no stored procedures, no triggers, etc...)

Before doing any of this, though, I must ask you a few questions:

1. Are you looking to design your whole database/application from scratch? Or are you looking for an application or framework that you can fit to your needs?

2. Will this application need to tie in to any other applications, such as legacy mainframe systems, remote web services, etc...?

3. Are you or is anyone in your organization familiar with PHP or SQL?

4. Is anyone in your organization familiar with Linux or Unix? (I would personally recommend NOT running a database on Windows XP -- at least use Windows 2000)

5. How big is your company? How many people will be accessing the database at one time?

6. What is your projected deployment time of this application? (are we talking months or weeks here?)

Without this information, it is impossible to give any kind of decent help. However, if your company is of a reasonably small size (less than 25 people), with no real knowledge of PHP/SQL, and you want quick deployment, I would suggest a couple of things:

1. See if you can find an existing application that can meet your needs. In-house development of such software can really eat up your time, and there are an incredible number of solutions already available. Unless your needs are truly complex and non-standard, this is usually your best bet.

2. I recommend you make sure that this application uses a fairly standard SQL DBMS on the back end, to make your data portable in the future)

3. Microsoft Access just might be the choice for you. It does allow rapid prototyping of a database. And, you can always connect it on the back-end to SQL Server, or even MySQL or PostgreSQL. (PostgreSQL requires a Unix server, though)

If you really want to use PHP, and other free tools, I suggest you use PostgreSQL as the DBMS, because it supports the SQL standards much better than MySQL, giving you a much more portable data environment. (so you can move to a commercial DBMS without too much pain). Don't try to build everything from scratch, though. There are a few application frameworks already built with PHP, such as phpGroupware.sourceforge.net, etc... which can help you speed up development of forms, interfaces, etc...

Hi Rycamor,

Thank you very much for your reply.

To answer some of your questions.

1. Are you looking to design your whole database/application from scratch? Or are you looking for an application or framework that you can fit to your needs?

To be honest, I haven't found anything on the web that would fit our budget. Best Software has something similiar to what we could use, but their multi-user package is looking $4000+. Also, it has a lot of unneccessary options that make the program look even more complicated for our existing employees.

Our current DOS based system is very simple. I'm thinking it shouldn't be to hard to develop something similiar but in a Windows Environment.

Basically looking for:

1.) A database that can support multiple users opening and writing into the same file at the same time. At least 5-10 users at a time.

2.) In the future, be able to have some sort of ecommerce and show inventory online.

3.) Hopefully Microsoft Windows Environment, since thats what the majority of our employees prefer.

Our current DOS system, we have to do a lot of things manually and on paper. I'm hoping to get this finished asap to cut our downtime and be able to support a larger customer base quicker.


2. Will this application need to tie in to any other applications, such as legacy mainframe systems, remote web services, etc...?

No not at all, only thing that will be needed is to either import the .DBF file records to a more advanced database, or have someone data entry the records over. But that shouldnt be a problem.

3. Are you or is anyone in your organization familiar with PHP or
SQL?

I am somewhat new to PHP/SQL, I will probably do as much as I can. If neccessary, hire someone to get it done right.

4. Is anyone in your organization familiar with Linux or Unix? (I would personally recommend NOT running a database on Windows XP -- at least use Windows 2000)

Most likely Windows, majority of the employee's are old timers and would have a hard time adjusting. Thanks for recommending Windows 2000.

5. How big is your company? How many people will be accessing the database at one time?

Small company, 5-10 will be accessing the database at one time, Maximum 15-20.

6. What is your projected deployment time of this application? (are we talking months or weeks here?)

Our current DOS Dbase III+ System works fine for right now, So this is not an immediate rush. Although in order to enhance our features and be competitive with the industry, this is something we must do.

As it is, we're still in the stone-age, using Purchase Order Books, File Cabinets and having to write up orders and quotations instead of inputing them into the computer.

Any replies or recommendations would be very much appreciated.

Hey Dan,

(FYI, you can go ahead and delete your duplicate post)

I provided consultation for a day with a company that uses the Best Enterprise Suite (formely called Sage). On superficial inspection, I was not so impressed. The front end to this application seemed almost slapped-together. The reports were done with Crystal Reports, but were badly formatted, and sometimes not even aligned properly. Maybe others here have had more(and better) experience with Best?

It's always tempting to think you can easily put together something that does what application X does, but this is the can of worms I was talking about. I speak from experience. If you go that route, it will probably take ten times the resources you expected. I speak from experience, believe me. For example, you mention $4000 as seeming kind of expensive to handle your needs. Well, even with my years of PHP experience, application libraries, shortcuts, etc... I still wouldn't be brave enough to commit to develop an accounting package for $4000.

Think about it this way: if you want to just handle fairly standardized business needs, then get some standardized software. If you want software that will run your whole business, then do a custom application. Even then, generally the smaller companies I know doing this have started with a standard accounting package, and figured out how to get their custom application to interface with it.

My advice: unless you and your company are fairly adventurous, and know exactly what you want, it's better just to stick with a tried-and-true standard accounting package, such as PeachTree, Acc-Pac, maybe even QuickBooks.

On another note, there have been a few attempts at creating an open source business accounting package. If you glance through www.freshmeat.net or SourceForge, you will see several such attempts, many of which are still in beta, or even barely functional. Some of these have been done with PHP and MySQL (I don't recommend MySQL). There have also been some more mature web-based accounting packages done with PostgreSQL and Perl, such as www.sql-ledger.com . The benefit of a web-based system is that it will support the Windows IE browser just fine, although you could run the software on a Linux server. (some of these open-source packages will actually run on a Windows server).

It also sounds like you could use some real education in database fundamentals, before taking on any of the above. Try to read some literature about SQL and relational database concepts. When choosing a package here are my recommendations:

1. SQL-based is better (and more portable) than some sort of proprietary database back-end. Even though SQL has its difficulties, your data is at least available in a fairly standardized manner. Many of the cheaper accounting packages use some other sort of database back-end, such as "multivalued", etc... approach. (Basically, if the literature tries to tell you they have improved on the "traditional relational database", then run the other way). In fact, beware of "exciting new concepts" in general; accounting is not exactly a new thing.

2. Look for a product that provides standard ways of importing/exporting data, or even better yet, to connect directly through ODBC. This will allow you to write applications that can talk to your accounting system.

Well I was also going to recommend SQL Ledger and also if rycamor recommends it you know it's good.

Don't know how much work or even if it would be possible to migrate your current stuff over there though.

If you don't have multiple locations where you store your inventory you can use Quickbooks Premium that would solve all your problems and if you store inventory in multiple locations then you would need to use Quickbooks Enterprise with advance inventory module.

Let me know if you need any further assistance.

you're kidding, right?

you honestly think that after almost ten years he's still waiting for help?

dream on

Sorry guys I didn't realize that I was responding to that old questions. As I just joined this forum, I didn't know how to respond.
I will be careful next time.

no problem, and thanks