3

I am modelling my database with respect to star schema with bridge tables.
My master Table looks like below. I need to convert this format to star schema.

MasterTable


StudentNumber | Subject1 | Subject 2 | Subject 3


1000 | Biology | Mathematics | Physics
2000 | Science | Mathematics | NULL
3000 | Biology | Mathematics |NULL


My db design has 3 more tables now.

  1. Fact Table
  2. Bridge Table
  3. Dimension Table

The structure and Data of these tables are like below

FactTable - Students


StudentNumber | SubjectGroupID


1000 | 1
2000 | 2
3000 | 3


BridgeTable - SubjectGroups


SubjectGroupID | SubjectID


1 | 100
1 | 200
1 | 300
2 | 400
2 | 200
2 | 100
3 | 100
3 | 200


Dimension Table - Subjects


SubjectID | Subject


100 | Biology
200 | Mathematics
300 | Physics
400 | Science


Inserting values into the Dimension Table

insert into Subjects(Subject) (
select distinct Subject1 from MasterTable
UNION
select distinct Subject2 from MasterTable
UNION
select distinct Subject3 from MasterTable)

Now, How to insert values into the Bridge table? Do we need to find all combinations of the 'Subjects' and form a group?.

How to do find possible combinations of the dimension table? IS there any other way to insert values into the bridge table?
Please temme how to insert values into the fact table as well in Microsoft SQL Server.
Thanks in anticipation.

4
  • I have edited for better understanding. Hope this looks better and understandable.
    – Nagej
    Commented Nov 14, 2014 at 8:54
  • Please have a read about flagging. You've flagged this post twice because you want an answer; this is not what flags are for. Commented Nov 18, 2014 at 15:23
  • @AaronBertrand Pardon me. Mistook the meaning of flagging.
    – Nagej
    Commented Nov 19, 2014 at 8:13
  • I would control this with a master insert stored procedure that kept track of the IDs involved. Pass these IDs to other SPs that perform table specific inserts for each table. Commented Dec 14, 2015 at 22:32

0

Your Answer

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