2

I'm a new member of Data warehouse. I'm designing model alter Star Schema. I have a 2 table: Contract table & Complain table. One Contract can have one or more value in Complain table. You can see 2 photo below.

in facttable, 1 contract only have one row. complain table can have 1 or more values for each contract_id. i've tried the bridges table, but it wasn't good enough. Thank all

http://prntscr.com/fgdnpb (Conplain table)

http://prntscr.com/fgdorh (Contract table)

My Skype: dinhhungdigital

Michael Tran
  • 41
  • 2
  • 3

2 Answers2

2

You are confusing fact table and dimension table. Your dimension should be "Contract", and your fact table should be "Complaint", connected on Contract_Id as one-to-many (as start schema requires).

Learn about Star Schema Design

RADO
  • 7,733
  • 3
  • 19
  • 33
  • It's a good idea, but i can't do it for my database. there isn't only 2 tables, actually there is 4 tables...: Requires, perform, bill... Each contract have one or multi value requires, bill...The relationship of contract table with these table are One-To-Many. So I want to Contract Table will be FactTable. – Michael Tran Jun 06 '17 at 07:21
  • Could you explain more what the tables you mention mean? Requires, perform, bill? From what you've said so far, I think RADO is right that contract should be a dimension. – Rich Jun 06 '17 at 07:37
  • @TrầnĐìnhHưng: I added a link to my answer that will help you learn about star schema design. Sorry to tell you, but you can't designate facts and dimensions as you please - their role is defined by their data structure. Fact tables accumulate transactions (in your case, complaints), while dimension tables provide context for these transactions (such as customers, locations, products, etc, in your case - contracts). Dim tables MUST have unique keys and MUST always be on the "1" side in relations, never on "many" side. – RADO Jun 06 '17 at 07:50
  • Thanks for your comments. Now, I understood your mean. I think i should review again my database. If you like, i want show my database for you. – Michael Tran Jun 06 '17 at 08:05
  • Requies table: http://prntscr.com/fgg91g Complain table: http://prntscr.com/fgga66 Bill table : http://prntscr.com/fgg9sh Contract table: http://prntscr.com/fggc7q – Michael Tran Jun 06 '17 at 08:15
  • The first 3 tables are designed as fact tables, Contract is a dimension. – RADO Jun 06 '17 at 09:04
1

You should have a DimContract & FactContract, as well as a DimComplaint & FactComplaint.

DimContract may consist of only the ContractID (degenerate dimension), but Code may also belong here as it looks dependent on the ContractID. FactContract connects all of the dimension attributes that describe the Contract

  • DimContractType,
  • DimDate as DimDateContractSigned
  • DimCustomer?
  • DimSalesPerson?

DimComplaint would likely consist of the ComplaintID and Code. If website is free-text it could be included here as well, if the user selects it from a list, it would be in the fact. FactComplaint connects all of the dimension attributes that describe the Complaint:

  • DimComplaint,
  • DimContractID,
  • DimServiceType,
  • DimTitle,
  • DimStatus,
  • DimDate as DimDateOfComplaint,
  • [DimWebsite if appropriate]

In this example, since the Complaint references a Contract and a Contract references a Customer, you can see the relationship between the customer and the complaint without needing a direct reference from FactComplaint to DimCustomer.

Wes H
  • 4,186
  • 2
  • 13
  • 24