Linking and Relationships

I haven't properly set up the prime input table or the output pivottables yet, but I think the charting is clearly depicted in the attached file. After watching several tutorials, I feel like what I am trying to do is possible, but I haven't quite nailed down the correct process.

There are a handful of potential output PivotTables ( building program, site program, department program, and occupancy report ). All would be PivotTables based on a single data input tab ( Project Program ), where every room / space in a project is listed and catalogued. However, such an input tab would have far too many columns, in my opinion. I feel it would be better to leverage the software to organize and link multiple tables, organizing and creating hierarchy for all the data. The correct phrase may be "linking," "PowerPivot," "data model," or even something else. I have watched many tutorials showing the power of linking tables. However, something that is also emphasized in many of those tutorials is the importance of not inputting data multiple times. If revisions are necessary, one should only have to change that data in one location. Thus, how do I input all the space / room names and numbers in one tab, but have them populate, relate, and link across multiple tables. Another example I read online used student identification numbers to track a student's test scores, assignments, and contact information ( in three separate tables ). One shouldn't have to manually type the student's identification number three times. In my mind, if I sort or filter the data input tab, any PivotTable output retains its integrity. However, if their are multiple tables used for input, I am at a loss both in theory and how exactly to set it up in Excel.

Any help would be awesome! Thanks everyone!