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.

Improve this question
4
  • I have edited for better understanding. Hope this looks better and understandable.
    – Nagej
    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.
    – Aaron Bertrand
    Nov 18, 2014 at 15:23
  • @AaronBertrand Pardon me. Mistook the meaning of flagging.
    – Nagej
    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.
    – Steve Mangiameli
    Dec 14, 2015 at 22:32

0

Reset to default

Your Answer

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