Clever Geek Handbook

Benefits of a database? Access, MySQL, msSQL or any others?

Dear all Stackoverflowers, I just started learning programming and now I am asking this question online based on a quote: not a silly question

My job is to develop a web based ordering system that wants a database system. Since I use Excel as a regular user in the office, I naturally turn to Access. However, most people say that access is very limited compared to MySQL or MSSQL or any other more professional database system.

But after developing some functionality for my purchase order system, I actually find that Access can fulfill my request. And I also tried to develop MSSQL, which I found it impractical to use.

I searched stackoverflow and didn't find any general answer about my doubts. Now, I sincerely hope that some experienced and professional developers can clear my doubts.

I now list some of the benefits of access that I don't think other database systems have. Hope you could help me find these benefits in others as well.

1. Access is portable, I can just copy a xxx.accdb file to my company and continue with development.
2. Access is easy to generate helpful table, for example, it will automatically generate a field that can automatically count, could be used as primary key value.
3. it is more compatable with Excel, to display and filter data.
4. importantly, it nerely needs no environment to setup, just needs MS Office to be installed. 
............others

However, I also find some points where MSSQL has the advantage:

1. security reasons
2. easy to backup, ( just use BACKUP..... sql statement to do it)
3. can edit stored procedure to save some functions to database
...............others

Specifically, I wish some friends could tell me how to make another database portable? since I usually work both at home and in the office. It is a headache to move MSSQL to my office as the MSSQL version is not the same.

Thanks everyone and best wishes :)

+3


source to share


1 answer


Microsoft Access

I've never used Access, but the main drawback I know of is that you must have access to the local filesystem, which means:

  • You need a shared filesystem
  • Someone with write access can delete everything
  • It probably doesn't have very good multiplayer performance due to file locking.

(Someone will correct me if I'm wrong about this)

Microsoft SQL Server

This is a pretty nice solution. I have been using it for several years and have found it to be good in most cases.

  • Pretty good performance
  • Comes with a nice GUI (SQL Server Management Studio)
  • Integrates with Microsoft domain logins (useful if you have Exchange I think)
  • There is a free version, but the non-free version is extremely expensive.


MySQL

I don't recommend MySQL. PostgreSQL and SQL Server are better suited in every way.

  • Good performance in some cases, very poor performance in others (terrible query planner)
  • Large community so easy to get help and tools.
  • Free

PostgreSQL

  • Consistently good performance (ability to use multiple indexes, has the best query optimizer I've ever used)
  • Several arcane syntaxes in some cases and fewer tools (less user friendly)
  • Free

Conclusion

Since you are already firmly in the Microsoft camp, I would just go with SQL Server if you're not worried about the price, in which case I would go with PostgreSQL. Access is an option that might be easier, but I think you will eventually regret it.

+1


source






More articles:


All Articles