TransWikia.com

tables inheritance stored procedure

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 CustRegistrationthe 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?

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP