You can add multiple standalone spreadsheet files as AppSheet tables. It's also possible to create AppSheet tables from different worksheets within the same workbook.
A single dataset used by AppSheet is called a table. The source of a table is a single spreadsheet. A workbook is a file that contains one or more worksheets; a worksheet is a single spreadsheet that contains cells organized by rows and columns.
Either approach works just fine, depending on your circumstances. The performance of the two options may differ based on the specific content. The AppSheet backend has to download the entire spreadsheet file, but if the same file (workbook) is referenced multiple times (via several tables referencing individual worksheets in the workbook), the backend will download it just once. On the other hand, multiple small files can be fetched and processed efficiently in parallel.
In most cases, you should pick whichever approach is most convenient for you to manage. However, if you use only two worksheets out of a spreadsheet file with a hundred large worksheets in it, you'd be better off splitting them into their own single files.
Apps Can Use More than One Table or Spreadsheet Data Set
In the Editor > Data > Tables tab, click on "+Table" to choose another spreadsheet. If you need to use a worksheet from the same workbook you're already using in your app, define the worksheet name (case included) in the Worksheet Name/Qualifier field. The worksheet automatically gets added to your app when you save your changes. You can then define views over this data as well.
Note that you'll need to create a new UX view pointing to the new spreadsheet to get that worksheet's data to display in the app.
See a good example of multiple tables and cross-table references in the Order Capture sample.