February 5, 2019 at 10:30 am
I have a scenario where an employer's contact address information is not required unless the employer billing is needed. Currently the address information for a contact is stored directly on the contact table with circular reference on the employer for the billing contact. The billing contact should be required when the BankAccountId attribute has been set. However, I feel that this approach puts data integrity at risk as there's no way at the database level that enforces the following:
1) Requiring the billing contact to be set when the bank account is set on an employer.
2) Requiring the address for a contact set when they're the billing contact
create table BankAccount
(
BankAccountId int identity(1,1),
--
constraint pk_BankAccount primary key (BankAccountId)
)
go
create table Employer
(
EmployerId int identity(1,1),
Name nvarchar(256) not null,
--
BankAccountId int null,
BillingContactId int null,
constraint pk_Employer primary key (EmployerId),
constraint fk_Employer_BankAccount foreign key (BankAccountId) references BankAccount (BankAccountId)
)
go
create table EmployerContact
(
ContactId int identity(1,1),
EmployerId int not null,
-- FirstName
-- LastName
AddressLine1 nvarchar(256) null,
AddressLine2 nvarchar(256) null,
City nvarchar(256) null,
-- etc
constraint pk_EmployerContact primary key (ContactId),
constraint fk_EmployerContact_Employer foreign key (EmployerId) references Employer (EmployerId)
on delete cascade
)
go
alter table Employer
add constraint fk_Employer_EmployerContact foreign key (BillingContactId) references EmployerContact (ContactId)
go
The change I'm proposing creates two new tables with 1-0..1 relationships.
create table BankAccount
(
BankAccountId int identity(1,1),
--
constraint pk_BankAccount primary key (BankAccountId)
)
go
create table Employer
(
EmployerId int identity(1,1),
Name nvarchar(256) not null,
--
constraint pk_Employer primary key (EmployerId)
)
go
create table EmployerContact
(
ContactId int identity(1,1),
EmployerId int not null,
-- FirstName
-- LastName
--
constraint pk_EmployerContact primary key (ContactId),
constraint fk_EmployerContact_Employer foreign key (EmployerId) references Employer (EmployerId)
on delete cascade
)
go
create table EmployerContactAddress
(
ContactId int not null,
-- AddressLine1
-- AddressLine2
-- City
--
constraint pk_EmployerContactAddress primary key (ContactId),
constraint fk_EmployerContactAddress_Contact foreign key (ContactId) references EmployerContact (ContactId)
on delete cascade
)
go
create table EmployerBilling
(
EmployerId int not null,
BankAccountId int not null,
ContactAddressId int not null
constraint pk_EmployerBilling primary key (EmployerId),
constraint fk_EmployerBilling_Employer foreign key (EmployerId) references Employer (EmployerId),
constraint fk_EmployerBilling_BankAccount foreign key (BankAccountId) references BankAccount (BankAccountId),
constraint fk_EmployerBilling_EmployerContactAddress foreign key (ContactAddressId) references EmployerContactAddress (ContactId)
)
go
I think this satisfies the requirements, but didn't know if the 1-0..1 tables was the proper solution. Would changing the table to structure to above introduce any new potential issues?
February 7, 2019 at 11:00 am
I think you have this right. Only one billing address wanted, and only available once the employer has been set and the bank account exists. Essentially you have a vertical partition of the table, which is what I'd do.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply