SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Design Help


DB Design Help

Author
Message
JJ-469859
JJ-469859
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 829
I am creating a change management system. Part of my design has a change request table and a contact table. Each change request has a technicianid, a managementid, a directorid, and a maincontactid. To find out all the info on the people for a change request, I have to join to the contact table four times.

From the contact table, I have a department table. So I have to join another four times to find the department of all the contacts for a single change.

Does this sound like a proper database design? Including the domain tables, I am up to 11 joins to find out the data I want on a particular change request.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39687 Visits: 32639
No, that's probably not a great way to go about it. Instead have a many-to-many join table between the two other tables. So you'll have a ChangeRequest table and a Contact table. Then you create a ChangeRequestContact table that has they key from each of the other two in it. This table should also have a link to a lookup table ContactType (or something, you name these things for your business). That way you can define how a given contact is associated with the change request. This will allow for you to add more and more contact types without having to redesign the system. I'd do the same thing for the department so that more than one department can be easily associated with a given request in varying roles. It makes for somewhat more complicated coding, but it's a lot more flexible and allows for change without having to re-structure your db and rewrite your code.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
JJ-469859
JJ-469859
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 829
Grant Fritchey (2/27/2009)
No, that's probably not a great way to go about it. Instead have a many-to-many join table between the two other tables. So you'll have a ChangeRequest table and a Contact table. Then you create a ChangeRequestContact table that has they key from each of the other two in it. This table should also have a link to a lookup table ContactType (or something, you name these things for your business). That way you can define how a given contact is associated with the change request. This will allow for you to add more and more contact types without having to redesign the system. I'd do the same thing for the department so that more than one department can be easily associated with a given request in varying roles. It makes for somewhat more complicated coding, but it's a lot more flexible and allows for change without having to re-structure your db and rewrite your code.


1) For the old database design, writes are fast and reads would be a little slower since they have to do so many joins. I have to build a report each month that shows the changes for that month. I figure 100 rows a month selected. Wouldn't read performance on only 100 rows be acceptable even if the database starts holding tens of thousands of rows?

2) I realize the design you suggest is much more flexible. But wouldn't I still have to do the same amount of joins to select the data? Below would be the insert statement. (assuming I don't have the ContactType lookup table)

CREATE PROCEDURE InsertChangeRequest_sp
(@TargetSystem varchar(40), @Description varchar(1000),
@TechnicianID int, @ManagementID int,
@DirectorID int, @MainContactID int)
AS
BEGIN TRAN

INSERT INTO ChangeRequest
(TargetSystem, Description)
VALUES (@TargetSystem, @Description)

DECLARE @ChangeRequestID int
SELECT @ChangeRequestID = Scope_Identity()

INSERT INTO ChangeRequestContact
(ChangeRequestID, ContactID, ContactType)
VALUES
(@ChangeRequestID, @TechnicianID, 'Tech')

INSERT INTO ChangeRequestContact
VALUES
(@ChangeRequestID, @ManagerID, 'Manager')

INSERT INTO ChangeRequestContact
VALUES
(@ChangeRequestID, @DirectorID, 'Director')

INSERT INTO ChangeRequestContact
VALUES
(@ChangeRequestID, @MainContact, 'MainContact')

END TRAN

-------------

Then the select statement would be

SELECT CR.TargetSystem, CR.Description,
(CT.FirstName + CT.LastName) as TechnicianName,
(CM.FirstName + CM.LastName) as ManagerName,
(CD.FirstName + CD.LastName) as DirectorName,
(CMC.FirstName + CMC.LastName) as MainContactName
FROM ChangeRequest CR
JOIN ChangeRequestContact CRC ON CR.ChangeRequestID = CRC.ChangeRequestID
JOIN Contact CT ON CRC.ContactID = CT.ContactID
JOIN Contact CM ON CRC.ContactID = CM.ContactID
JOIN Contact CD ON CRC.ContactID = CD.ContactID
JOIN Contact CMC ON CRC.ContactID = CMC.ContactID
--plus four more joins for department
WHERE ScheduleDate is this month

Does this sound right?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39687 Visits: 32639
Yeah, you'll have a few joins, it's still not that many and the indexing should be very simple. The one thing I'd suggest, instead of trying to pivot the data into columns, return it as rows, you'll have fewer joins and simpler queries that way. You can change the layout on the client side so that it's pivoted.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
StephenMarais
StephenMarais
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 54
The ChangeRequestContact table is the best way to handle this type of data. As an added daat integrity measure i would place a constraint on the table only allowing one contact per role type per change request.

Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com
JJ-469859
JJ-469859
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 829
Grant Fritchey (3/6/2009)
The one thing I'd suggest, instead of trying to pivot the data into columns, return it as rows, you'll have fewer joins and simpler queries that way. You can change the layout on the client side so that it's pivoted.


Can you explain this a little more? I am not following how I could pivot into columns instead of rows. What would my query look like?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39687 Visits: 32639
The data would be something like

Name, Type
Joe Plumber
Tim Project Manager
Tom Developer
Etc.

Instead of what you're trying which is:

PlumberName, Project Manager Name, DeveloperName
Joe Tom Tim

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search