AnsweredAssumed Answered

Multiple addresses

Question asked by piaccounting on Sep 22, 2017
Latest reply on Sep 23, 2017 by piaccounting

Hello everyone,

I am designing a solution where I have Associates in an Associates Table. Each associate could have a number of business connections such as being a Customer, a Supplier, a Member or an Employee (or any other category). I have these categories in a Business Category table.

 

I now have a link table called Business Associations which contains (amongst other fields) the two foreign keys to be able to match up the Associate with one or more categories.

 

My thoughts now turn to the addresses. I was considering whether the addresses should be on the Associates table or on the Business Associations table. However as it is possible to have multiple addresses I am tending towards putting all addresses into an addresses table. The relevant business association (e.g. customer or supplier) would then reference to the required address from this table.

 

I want to be able to restrict addresses to particular associates so I have included a foreign key on the addresses table (_fkAssocID) to link to the associate table. Thus once an address is set up for an associate it can be used by that associate for any other business category (e.g. supplier, employee...)

 

My question therefore is whether this is a sensible way to approach this or is there a better or preferred way?

 

Thank you for your guidance and help.

 

 

tblAssociate
ID
CreateDate
ModifyDate
CreateUser
ModifyUser
Code
Name
VATReg
CompanyNo
CharityNo

 

 

tblBusnAssoc
ID
CreateDate
ModifyDate
CreateUser
ModifyUser
_fkAssocID
_fkBusnID
Status
OnStop
ContactName
Salute
FirstName
Surname
Title
_fkAddressesID
_fkTerms
Days
CreditLimit
_fkNominal
_fkBank
_fkVATCode
UTR
NINo
PassCode1
PassCode2
RepCode1
RepCode2
RepCode3
RepCode4
RepTxt1
RepTxt2
RepTxt3
RepTxt4
RepDate1
RepDate2
RepDate3
RepDate4
RepNum1
RepNum2
RepNum3
RepNum4

 

 

tblBusnCat
ID
CreateDate
ModifyDate
CreateUser
ModifyUser
Category

 

 

 

tblAddresses
ID
CreateDate
ModifyDate
CreateUser
ModifyUser
_fkAssocID
AddrNo
Name
AddressLine1
AddressLine2
AddressLine3
Town
County
Country
PostCode
Phone
Fax
Mobile
Phone4
Email
Website

Outcomes