Q: How to restrict access to facts based on user access across differing dimensions in MySQL Overview
I am using MySQL 5.7 and I have a model where I need to be able to identify gym locations that each member can access.  These members can get access to one or many gyms based on attributes of each location such as its parent gym, affiliations, etc.  For example, as a member I may be able to access a specific location by nature of being a member of that gym's parent company (e.g. being a member of Planet Fitness gives me access to all Planet Fitness locations) or I may have access to a specific Planet Fitness location that my employer pays for.  For this contrived example, we can say that each location has a 1:1 relationship with each of these parent attributes.
The locations are a fact table within a star schema and the levels at which access is defined (e.g. by gym, healthcare provider) are dimensions of that fact table.  I've included SQL below to setup a DB with these scenarios and want to know what, if any, models or patterns are well suited to describe this type of relationship.
Example ERD
Desired Outcome
Let's assume we have two members, Bill and Ted.  Bill is a member of Axiom, a Microsoft Employee and a member of Blue Cross.  Ted has a membership to Planet Fitness.
Given these records:
-----------------------------------------------------------------------------
| location_id   |   gym_name       |    company_name    |   provider_name   |
-----------------------------------------------------------------------------
| 1             |   Planet Fitness |    NULL            |   NULL            |
| 2             |   Planet Fitness |    Microsoft       |   NULL            |
| 3             |   Planet Fitness |    NULL            |   Blue Cross      |
| 4             |   Planet Fitness |    Microsoft       |   Blue Cross      |
| 5             |   Axiom          |    NULL            |   NULL            |
| 6             |   Axiom          |    NULL            |   Blue Cross      |
| 7             |   Axiom          |    Microsoft       |   Blue Cross      |
-----------------------------------------------------------------------------

I want to be able to do something like:
SELECT loc.*
FROM locations loc
JOIN member_access USING (gym_id)
WHERE member_id = ?

and get a unique list of locations for Bill or Ted.  I would expect that:

*

*Bill has access to six locations (the three Axiom locations + the three Planet Fitness locations that give access to Microsoft Employees and/or Blue Cross members)

*Ted has access to four locations (the locations with the Planet Fitness gym_name)

Things I've Tried

*

*storing every user:gym relationship but the table is simply too large (billion of records) and the data has too much churn to meet SLAs.

*maintaining separate tables for user:gyms, user:providers, user:companies relationships, but the results need to be the UNION of these values (i.e. all unique locations I have access to based on my gym membership + all locations my company gives me access to + all locations my healthcare provider gives me access to).  The response that provides this list of available locations is paginated so I don't want to have to UNION thousands of records every time I need to return a page of ~20.

Example DB
drop database if exists so_gyms;
create database so_gyms;
use so_gyms;

CREATE TABLE members (
    member_id smallint not null auto_increment
    , member_name varchar(64)
    , PRIMARY KEY (member_id)
);

CREATE TABLE gyms (
    gym_id smallint not null auto_increment
    , gym_name varchar(64)
    , PRIMARY KEY (gym_id)
);

CREATE TABLE companies (
    company_id smallint not null auto_increment
    , company_name varchar(64)
    , PRIMARY KEY (company_id)
);

CREATE TABLE providers (
    provider_id smallint not null auto_increment
    , provider_name varchar(64)
    , PRIMARY KEY (provider_id)
);

CREATE TABLE locations (
    location_id smallint not null auto_increment
    , gym_id smallint
    , company_id smallint
    , provider_id smallint

    , PRIMARY KEY (location_id)
    , FOREIGN KEY (gym_id) REFERENCES gyms (gym_id)
    , FOREIGN KEY (company_id) REFERENCES companies (company_id)
    , FOREIGN KEY (provider_id) REFERENCES providers (provider_id)
);

INSERT INTO members (member_name) VALUES ('Bill'), ('Ted');
INSERT INTO gyms (gym_name) VALUES ('Planet Fitness'), ('Axiom');
INSERT INTO companies (company_name) VALUES ('Microsoft');
INSERT INTO providers (provider_name) VALUES ('Blue Cross');
INSERT INTO locations (gym_id, company_id, provider_id) VALUES
(1, null, null), # a Planet Fitness location
(1, 1, null), # a Planet Fitness location w/ Microsoft access (pun unintended)
(1, null, 1), # a Planet Fitness location w/ access for Blue Cross members
(1, 1, 1), # a Planet Fitness location w/ access for MS employees & Blue Cross members
(2, null, null), # an Axiom location
(2, null, 1), # an Axiom location w/ access for Blue Cross members
(2, 1, 1); # an Axiom location w/ access for MS employees & Blue Cross members


A: just to clarify, are you trying to build a set of tables to answer this one specific question or are you trying to build a generic star schema to answer this and other similar questions?
If you just want to answer this one question then i would build a set of tables in 3NF, something along the lines of:
USER 1:M COMPANY 1:M GYM
and to make life easier add a dummy record for each gym  in the COMPANY table so that you have the same hierarchy regardless of whether a user is linked directly to a gym or via a company.
If you want to design a star schema then you need to decide what your fact table measure(s) and grain are and then build the design up from that.
If you have a properly designed star schema and end up with billions of records (which is easily possible) then if your DBMS or ETL can't handle it then that seems to be an issue with your tool selection (or possibly its implementation/configuration)
