October 20, 2009 at 4:27 am
Folks,
I have a situation where a customer has an existing and many-columned table which was refactored/split into 3 tables. Names here are replaced but I hope you get the gist.
The original table <Project> needed to have columns "sub-classed" as it where into 2 specialisation tables <VoluntaryProject> and <CommercialProject> with a 0-1 relationship with each. Each project has either a related voluntaryproject record OR a related commercialproject record OR NO related record for either of these two tables.
So the tables look something like this.
<Project>
fields:
projectid,projectname,projectdate, projectcontact
<VoluntaryProject>
fields:
voluntaryprojectid, projectid, voluntaryorg, voluntarymembercount
[voluntaryproject.ProjectId is a foreign key to project.projectid and is unique]
<CommercialProject>
fields
commercialprojectid, projectid, commercialsales,commercialstudies, commercialmembership
[commercialproject.ProjectId is a foreign key to project.projectid and is unique]
The unique-foreign key in both the sub-tables ensure that only one project related row can be created for either commercialproject or voluntaryproject.
The customer needs to ensure that a project can only have a related voluntaryproject or a commercialproject. I want to avoid the situation where a project has both a voluntaryproject AND a commercialproject record and I want to enforce this integrity at a database level. Please note that in reality both commercialproject and voluntaryproject each have many fields.
If folks could offer advice on a possible design refactor here, I would appreciate that too. I recognise that the above is not a good design, however the customer wants to avoid much normalisation at this point.
Thanks
M
October 20, 2009 at 4:41 am
Insert trigger on each of the sub tables. Should have minimal performance impact because they only get fired on the initial insert. If a record already exists in the other sub table it rolls back the insert.
CREATE TRIGGER dbo.trInsVoluntaryProject ON dbo.VoluntaryProject FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
IF EXISTS ( SELECT 'X'
FROM inserted i,
dbo.CommercialProject cp
WHERE i.ProjectId = cp.ProjectId )
BEGIN
RAISERROR( 'Already has Commercial Project Metadata', 16, 1 )
ROLLBACK TRAN
END
END
GO
CREATE TRIGGER dbo.trInsCommercialProject ON dbo.CommercialProject FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
IF EXISTS ( SELECT 'X'
FROM inserted i,
dbo.VoluntaryProject vp
WHERE i.ProjectId = vp.ProjectId )
BEGIN
RAISERROR( 'Already has Voluntary Project Metadata', 16, 1 )
ROLLBACK TRAN
END
END
GO
CEWII
October 20, 2009 at 4:43 am
Also as a side note, it really isn't that bad of a design.. It actually may make things easier, a common table that has stuff that both have and then a table with specifics.. I can see that..
CEWII
October 20, 2009 at 6:34 am
Just a quick observation... voluntaryprojectid and commercialprojectid are superfluous... projectid should be the primary key (and foreign key) for your specialisation tables.
October 20, 2009 at 9:47 am
Good catch Ian, I agree.
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply