Relation ship build query

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.
LVL 10
ukerandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
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
ukerandiAuthor Commented:
"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
ukerandiAuthor Commented:
cust_seq also can't be unique
0
Haris DulicCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.