We have a proposed restructure of our CRM containing almost 500 databases containing ~25 tables each into a single multidimensional database with one giant fact table linking everything together.
I have looked at the star schemes but there will be so many different star points that a multidimensional data type seems more fitting.
Here is a rough generalized mock-up structure:
id | parent_table | parent_id | child_table | child_id | relate_type
id | first_name | last_name | birth_date
id | name | lat | long
id | slug | tag_name
id | line_1 | city | state | country_id | zip
id | country_id | number | phone_type_id
id | name
id | phone_type
There are virtually an unlimited amount of joins between just these tables as people and organizations can both have addresses and phone numbers and people can have other people and organizations can have people and people can have organizations.
There is a small amount of data that can have foreign keys such as the phone types and countries.
Are there other downfalls to this data type other then putting the crux of security at the data API level instead of in the database itself.
Are there any caveats in not being able to have foreign keys between the fact table and the dimensions?