技术选型咨询：Excel与MySQL/MongoDB在JavaFX GST申报系统中的选择

Hey there! Let's break down your options based on the specific needs of the GST filing app you're building for your dad. Your use case—simple data entry in a JavaFX TableView, Excel exports for your CA, plus import/export/edit functionality—gives us clear criteria to evaluate each storage solution.

Excel as a Storage Solution #

Pros #

• Zero setup overhead: No need to install, configure, or maintain a database server. Your dad can start using the app immediately without extra technical hoops to jump through.
• Seamless CA workflow alignment: Since you're already using Apache POI to generate Excel files for your CA, storing data directly in Excel skips an extra conversion step for day-to-day records. Your dad might also be familiar with Excel, making it easy for him to manually check or tweak data outside the app if needed.
• Portability: Excel files are easy to share, backup, and move between devices without relying on a network or server.

Cons #

• Limited scalability: If your dad's business grows and generates hundreds or thousands of GST records, Excel will slow down significantly—loading, searching, and editing large files becomes clunky and unreliable.
• Data integrity risks: Excel files can get corrupted easily (especially if edited outside your app), and there's no built-in support for transactions or strict data validation. This could lead to accidental data loss or inconsistent records.
• Clunky querying: Filtering, sorting, or building complex reports (like annual GST summaries) will require you to implement all that logic manually in your JavaFX app, since Excel's native tools aren't designed for programmatic, large-scale data manipulation.

MySQL/MongoDB as Storage Solutions #

These two are both database systems, with key differences in data models:

• MySQL: Relational database, perfect for structured GST data (fixed fields like invoice number, date, amount, tax rate, etc.).
• MongoDB: NoSQL database, ideal if you think your data structure might evolve (e.g., adding new fields for specialized GST categories later without modifying a rigid schema).

Pros #

• Scalability and performance: Databases handle large datasets effortlessly. Even with years of GST records, searching, sorting, and generating reports will stay fast and efficient.
• Data integrity and security: Built-in features like transactions, field constraints, and user authentication ensure your dad's data stays safe and consistent. You can also set up automated backup routines easily.
• Advanced querying: Leverage SQL (for MySQL) or MongoDB's query language to build complex, app-integrated reports—like monthly GST liability calculations or vendor-wise tax summaries—without manual Excel work.
• Future-proofing: As the app evolves (e.g., adding multi-device access or user roles), a database backend will make expanding features far simpler than working with Excel files.

Cons #

• Setup and maintenance: Your dad will need to have the database server installed (or use a cloud instance, which adds its own complexity). You'll also have to handle connection management, backups, and occasional troubleshooting.
• Learning curve: While you'll handle the development, your dad might not be familiar with databases—so you'll need to make sure the app's UI hides all that technical complexity.
• Minor conversion step: You'll still need to use Apache POI to export database data to Excel for your CA, but that's already part of your plan, so this is a small tradeoff.

Recommendation Based on Your Use Case #

• Go with Excel if: Your dad's business has a small volume of GST records (e.g., 50-100 entries per month), he prefers keeping things simple, and you don't anticipate needing advanced reporting or scalability anytime soon. It's the quickest way to get the app up and running with minimal fuss.
• Go with a database (MySQL/MongoDB) if: You expect the number of records to grow over time, want to build in advanced reporting features, or prioritize long-term data integrity and security. MySQL is a solid pick if your GST data has a fixed, structured format; MongoDB works if you think the data model might need flexibility later.
• Hybrid approach (bonus): You could even use both—store the master data in a database for efficient management, and let the app export/import Excel files for sharing with the CA or manual edits. This gives you the best of both worlds.

内容的提问来源于stack exchange，提问作者heethjain21