[quote=191591:@Kayla G]Wow! Thanks for all the responses!
Kem, yes, I’m creating this as part of a software for thousands to use. Single user desktop app. I do have a question though. For the software, the user will start a business file. This business file will include the product database and also a client database, but if they wanted to add a completely separate business for the software, generating a completely new product/client database for that separate company, would that be a problem? When the user would start a new company they would click File ->New…starting a new file/company. Would the separate information (separate databases) be difficult to construct? Thanks for your help!!
Beatrix, I personally don’t see it becoming the GB rage, but maybe the user has a super long list to input. So I’m thinking the bigger the better. I’ll say the GB range. I’m hoping not too complete. Just inputs from the user (Product name, product code, price, qty, etc.) Thanks for the recommendation. I’m definitely going to check out SQLite, as it seems to be highly recommended. I’ve heard of Valentina (I found this link on YouTube last night: Installing Valentina for Xojo), but I’m still researching it. It looks very useful!
Thanks also Jean-Yves and Joost! I’m going to research SQLite.[/quote]
Welcome to Xojo.
Based on the information you provided, SQLite is likely the best choice. If your application grows to become multi-user then PostgreSQL will likely be the best choice. Both database engines are free, powerful and well supported by Xojo and this community.
Consider keeping all the application data in a single database with many tables. Doing so will make it easier to retrieve when you are combining related values in a single query. For instance, you may want to present a list of customers that purchased certain merchandise, share customer information between businesses or compare the sales of one business to another.
When designing your tables, be sure that each one has a field with a value that uniquely identifies the record. These are called primary keys and are usually numeric. In SQLite, it is an Integer Primary Key. You can use those values as a way to cross-reference records across tables. For instance, each sales transaction would have references to a customer and merchandise. More precisely, each sales transaction parent record would have a reference to a customer record and each sales transaction child record would have references to the sales transaction parent record and a merchandise record. There could be many sales transaction child records for each sales transaction parent record. This is all part of Database Normalization and something important to learn a bit about before designing your tables.