Database Administrators Asked by juclart ngouedi on September 10, 2020
create table CustomerTypes(
CustomerTypeID int primary key identity(1,1),
CustomerType varchar(11)
);
insert into CustomerTypes(CustomerTypeID,CustomerType
select 1, 'Regular' union all
select 2, 'Business'
create table Customers(
ContactID int primary key identity(1,1),
CustomerTypeID int references CustomerTypes(CustomerTypeID),
FirstName varchar(25)not null,
LastName varchar(25)not null,
DOB date not null,
Telephone varchar(18),
Notes varchar(250),
AddDate date not null
constraint cust_AltPK unique (ContactID,CustomerTypeID)
);
create table Regular_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 1 persisted, --regular
Specification varchar(45),
Date_Joined date,
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);
create table Business_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 2 persisted, --Business
Business_Name varchar(30)not null,
Business_Type varchar(30),
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);
create procedure CustReg
@custType varchar(11),
@custTypeID int,
@firtName varchar(25),
@lastName varchar(25),
@dob date,
@telephone varchar(18),
@notes varchar(250),
@addDate date
AS
BEGIN
SET NOCOUNT ON
--SET IDENTITY_INSERT CustomerTypes ON
INSERT INTO CustomerTypes values(@custType)
SELECT 1,'Regular'union all
select 2, 'Business'
--SET @CustTypeID = SCOPE_IDENTITY()
--SET IDENTITY_INSERT CustomerTypes OFF
DECLARE @ContactID int
INSERT INTO
Customers(CustomerTypeID,FirstName,LastName,DOB,Telephone,Notes,AddDate)
VALUES(@CustTypeID,@firtName,@lastName,@dob,@telephone,@notes,@addDate)
SET @ContactID = SCOPE_IDENTITY()
end
exec CustRegistration 'Business','5','Jean','Bruno','2012-03-09','073098743','business trip','2017-07-04'
After running exec CustRegistration
the Customer table is populated with 5 in CustomerTypeID column. I was excepting to get a warning message of foreign key conflict in table customerType. What am I doing wrong?
It looks possible that you have inserted more into the customer type table than you think via the CustReg proc as CustomerType is not unique and the proc does
INSERT INTO CustomerTypes values(@custType)
....
If I'm honest the model could do with some work as the alternate key of the customer table appears to contradict the primary key.
At the moment customer 1 and 2 could be the same customer of different types. That may sound like it makes sense but I'd suggest it is incorrect and allows for a many to one relationship between parent and child despite trying to enforce the type. In general super types should in general be unaware of subtypes.
As your question is around inheritance then it would follow that
A business customer is A customer
A regular customer is A customer
A customer is either A regular customer or A business customer or potentially both.
I'd remove the customer type table/enumeration and the calculated customer type columns and just use a Customer table and the two child tables.
The primary key of the child tables should be the 'inherited' customer ID i.e. the parent table should have a one to one relationship with any child table.
Use the fact that the customer ID exists or is in the child table to determine what type or types of customer they are.
If you must enforce that the customer can only be of one subtype this could potentially be done via an indexed view as a union of the customer ids in the child tables. That's a theory and not something I've tried.
Unrelated but notes about a customer would probably be better served as a separate table also.
Answered by Tomas Ingram on September 10, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP