Foreign Key referring to more than one table

Get Help
1

Hello, we have one fact table and two dimension tables for projects.

One dimension table is the main Project Dimension table, where you can expect all the usual stuff. It has the Project ID as a Primary Key.

The other dimension table is the Project Type Dimension table. The Project Type field contains different types project has been tagged as. One project can be tagged as multiple types (hence why it was not included in the main Project dimension table). The users should be able to filter charts by single Project Types, not just the combinations of Project Types you would get if you just stored them as a string aggregation.

You can image this table as:
Project ID | Project Type
Project 1 | Type 1
Project 1 | Type 2
Project 2 | Type 3

The Project Fact table has a Project ID field that has been set to a Foreign Key and is pointed towards the Project ID field in the Project Dimension table. Is there any way I could also point it towards the Project Type Dimension table as well?

I'm thinking right now an easy solution would be to add a duplicate Project ID field to the Project Fact table, but that feels a little silly... Any suggestions would be greatly appreciated!

2

This is a standard Alias issue in SQL. Best to have a view on top of your fact table so that you can easily add alias columns.

3

Sorry, can you please expound on that? I'm not using custom SQL for my charts.

4

Nothing to do with using custom SQL.
Instead of using the tables from your database, create a view in your database and use that instead. You can then add the extra column required to the view.