Designing a database for Large Data and Multiple Users [closed]

I am designing a project that can require thousands of records per Admin(I estimate within a couple years I will see over 100,000 records in the database) and all records are unique to each Admin. (ie I should never access your data and you should never access mine). Each Admin can create multiple users that gives them access to specific data, but only data that is apart of their permission group. What is the best organization strategy to allow for “Big Data” in this sense?

Imagine my project were to help a financial advisor and a client keep track of all their purchases. The financial advisor can view all their clients info, Each sub client can only view their info (unless permissions have been given otherwise). and multiple Financial Advisors can use the same software running on a centralized server. The only assumption we have to make is that each Purchase is an object that could contain a sizable amount of data.

I would likely have a DB for login and certificate information. but once a user is logged in lets assume they have a UserID.

Using that UserID I know I can track object permissions (Server Side). But if I have over 100,000 records I can see performance issues attempting to retrieve all records that a specific user has access to on login. I would likely be using React with a Redux store so that once I retrieve all my data once I will not need to worry about consistently retrieving data (I can also add a flag in the database that lets a user know if their data is up to date).

This is my thought:

• 1 database to hold user login and certificate info
  • This is for security purposes
  • A unique server will be running for all login requests
• 1 database to hold the accessible data
  • This database contains:
    • 1 table for resources (configurable user info, est 5000 records)
      • Could be sorted in such a way that each Admin has a reserved set of records
    • 1 table for infoObject (100,000+ records)
    • additional tables for other accessible data not related to the infoObject (est 5000 records each)
  • User the UserId to retrieve their specific resource
    • contains all UserId’s this user has permission to view info for
  • Retrieves all records associated with their ID and all IDs they have access for.

NOTE: I have the thought to use an SQL database for this, if you believe otherwise please state that!

Thank You!

Answer

For large data-sets, I would suggest table partitioning and indexing.
Check partitioned tables and indexes for Oracle DB, the same can be achieved for other SQL DBs too.