Understanding best architecture for editing/viewing/adding records, file storage, and basic queries RRS feed

  • Question

  • Hi there,

    I've been tasked with a new project that has the following requirements:

    • A clean GUI for viewing/editing records as well as the ability to add new records
    • Ability to add attachments that are directly connected to specific records
    • Ability to query the data in basic ways (all records that have the following column > 500 or all records that return a '1' for a specified column, etc.)
    • Must support a multi-user environment (maximum of 5-6 total users at any one time)
    • Ability to sum selected records (sum up column X for all records that meet a condition = Y)

    Other information:

    • 4 or 5 total tables
    • Approximately 40 columns with CHAR, BOOLEAN, INT, FLOAT, and DATE data types for the primary table with each other table having around 5-10 columns (similar data types)
    • Fairly small data volumes (50 MB to start with another 20 MB or so each following month)

    Plausible ideas:

    • Use Microsoft Access as the front-end and link to an Azure SQL database in the back-end. Forms can be built to view/add/edit records as well as buttons for specific pre-built queries. My problem with this one is I can't figure out how I would solve the attachments problem. Is there a way to connect Microsoft Access to Azure Blob Storage or Azure File Storage?
    • Use PowerApps as the front-end and link to an Azure SQL database in the back-end. With this one, I again can't figure out how to properly solve the attachments problem. I know there is an 'Add Picture' control but from some quick research, it doesn't seem like there is current functionality for adding PDFs. 

    If I can clarify anything further or if there's a better place to post this, please let me know.

    Thanks in advance!

    Thursday, June 7, 2018 7:49 PM