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?