CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Paul
Paul

Reputation: 314

SQL Fact table-dimension

LARGE RESOLUTION (click here)

enter image description here

How can we put to the fact table called Patent a foreign key for the primary key of the Inventor table (InventorID) preserving the right connection of the tables as noted by the relation between the primary key of the table Patent called Patent and its foreign key Patent of the table Inventor. We are doing this to create a dimension using the inventor table for the fact table patent.

Upvotes: 0

Views: 1188

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Your problem is that you have a many-to-many relationship between patents and inventors. You cannot readily implement this with a simple snowflake schema.

Basically, you need to introduce a new table at the patent-inventor level, to handle the many-to-many relationship between these entities.

You can think of this in two ways. The first is that you have a star flake schema with a fact table at a new level, patent-inventor. The patent and inventor levels are then dimensions off of this table.

The second is as a multiple fact tables, with the patent-inventor table connecting them.

What is important is that other dimensions that may be common to both patent and inventor are shared -- dimensions such as time and geography.

Upvotes: 2

Related Questions