Solved

Relation ship build query

Posted on 2014-10-19
5
140 Views
Last Modified: 2014-10-23
I have 2 tables
one is Fact Table and Customer
Fact Table fields are



Originator nvarchar(60),
[Customer Number] nvarchar(7) ,
Cust_Seq int FOREIGN KEY REFERENCES Customers(cust_num),

[Item code] nvarchar(10) FOREIGN KEY REFERENCES Item([Item code]),
[DateKey] INT FOREIGN KEY REFERENCES DimDate(DateKey)

and
Customer Table
cust_num nvarchar(7),
cust_seq INT PRIMARY KEY,
Company_name  nvarchar(60),
Address1  nvarchar(60),
Address2  nvarchar(60),
Address3  nvarchar(60),
Address4  nvarchar(60),
City  nvarchar(60),
Zip_Code  nvarchar(60),
County_State  nvarchar(60),
Country  nvarchar(60),
Telephone  nvarchar(60),
Email  nvarchar(60)


My question is

Customer Number is can't take unique,but custpmer sequence number can take unique.
becuase one customer has many orders and but customer sequence number will generate unique number according to orders(0,1,2,3...etc)

if i comapre with Fact table cust_seq field and customer table cust_seq is can't equal,becuase i will get wrong results
for example customer1 and customer 2 has same sequence number.

So can some one show me how to combine Customer table and Fact Table using which Fields(This for Dataware House).
and i need custo_seq becuase some time some time same customer address is different according cust_seq number.
0
Comment
Question by:ukerandi
5 Comments
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 40390364
I admit I do not clearly understand the question.

"for example customer1 and customer 2 has same sequence number."
-- In general, when a SEQUENCE object is used to populate a unique value,  it would be illogical and a design flaw.  DML with INSERT INTO ... (customer_seq#.nextval, ....) will always return a unique number (within the constraints of the object's definition).

So, your primary key on the table is the customer sequence#, yes?

The customer sequence# is entered on the fact table as a foreign key (relational parent-child integrity), yes?
With a difference sequence generator for orders, all order# will be unique.
0
 
LVL 10

Author Comment

by:ukerandi
ID: 40390690
"for example customer1 and customer 2 has same sequence number.

Example :
Customer No                 cust_seq
E0001                             0
E0001                             1
E0001                             2
E0002                             0
E0002                             1
E0002                             2
0
 
LVL 10

Author Comment

by:ukerandi
ID: 40390694
cust_seq also can't be unique
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40391154
Since the combination of the customer_no and cust_seq is uinique you can join the tableson those two columns, i.e.

select colums_you_need
from Fact_table f left join customer_table c on f.[Customer Number] =c.cust_num and f.Cust_Seq =c.cust_seq

Open in new window

0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40391473
Your Customer Table should have a composite primary key on (cust_num, cust_seq).
So you can have a composite foreign key referencing both fields:
ALTER TABLE Customer ADD CONSTRAINT PK
  PRIMARY KEY (cust_num, cust_seq)
GO

ALTER TABLE Fact ADD CONSTRAINT FK
  FOREIGN KEY ([Customer Number] , cust_seq) REFERENCES Customer (cust_num, cust_seq)

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now