1

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:

pivot_table

id | parent_table | parent_id | child_table | child_id | relate_type

person

id | first_name | last_name | birth_date

organization

id | name | lat | long

tag

id | slug | tag_name

addresses

id | line_1 | city | state | country_id | zip

phone_number

id | country_id | number | phone_type_id

country

id | name

phone_types

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?

3
  • Always use foreign keys if they are warranted. Data integrity is critical to a database providing timely and (much more importantly) correct results!
    – Vérace
    Oct 29, 2019 at 8:08
  • @Vérace true foreign keys could not be made in a multidimensional data with a single fact table unless that fact table included a column for each parent table and a column for each child table. This then negates the advantage of having a single fact table and would complicate queries. I understand that using a foreign key everywhere available such as in the example for country_id .
    – amaster
    Oct 29, 2019 at 15:45
  • @Vérace So back to the original question. Would this schema not be recommended and instead opt for a global data type with every unique relationship defined in it's own fact table.
    – amaster
    Oct 29, 2019 at 15:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.