0

In my scenario I have a table tblCity that has two columns as foreign keys CompanyRef and BranchRef, also they together are unique.

And I'd add one unique key to use as primary key [ID]

And in other table called tblCustomer I need to use tblCity as foreign key.

My problem is that I really need that ID column or I should use two foreign keys as primary key? In second case I must use three column as foreign key (CompanyRef, BranchRef, CityRef) in tblCustomer or what?

Which one of these methods is right for my problem?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Pouria Sharif
  • 156
  • 1
  • 15

2 Answers2

1

So, just to make things clear a little bit in your question (I hope I got it right):

tblCity

CityId INT                           -- is part of the separate PK option
CompanyRef INT, FK -> tblCompany
BranchRef INT, FK -> tblBranch

tblCustomer

CustomerId INT                      -- not interesting here
CityRef INT FK -> tblCity           -- is part of the separate PK option
CompanyRef INT                      -- part of the alternative
BranchRef INT                       -- part of the alternative

I can't tell which one is best performance-wise (that's more a DBA question), but from a developer perspective, I would advice for having a single column PK for City:

City sounds like a quite generic concept. It might be needed in the future, so dragging two columns in each other table referencing it, means that each JOIN will be on those two columns.

The final solution could look like this:

tblCity

CityId INT PRIMARY KEY IDENTITY(1, 1),     
CompanyRef INT, FK -> tblCompany
BranchRef INT, FK -> tblBranch
UNIQUE (CompanyRef, BranchRef)      -- acts as a constraint, but also an index

tblCustomer

CustomerId INT                      
CityRef INT FK -> tblCity

Side note: Hungarian notation seems quite discouraged these days - see this very popular question and its answers.

Also, I would advice to keep the same column name for the same thing. E.g.

CompanyRef -> CompanyId (or whatever the PK is named)
BranchRef -> BranchId
Community
  • 1
  • 1
Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
0

you need to creat relationship

base of what type of relationship you need to use

primary key and foreign key = one to money

primary key and primary key = one to one

foreign key and foreign key = many to many

porat
  • 1
  • 1
  • that's not solve my problem,when we taking about City and Customer, that's obvious relation is one to many, but thanks for comment. – Pouria Sharif Apr 24 '16 at 20:22