Normalization/Design Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Normalization/Design Question

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-17 : 23:09:36

I've been tetering back and forth on a design issue. Largely because I've seen this idea implemented two ways routinely on various applications. While I grasp the general idea of normalization, I am still struggling with the the 1st, 2nd and 3rd normal forms.

I have two designs (declared as table variables for quick examples). I haven't set up all the pk/fk relationships/contraints or indexes for expedience, but I'm hoping the sample code will provide enough information.

If you look at the two examples, I didn't get much difference in performance on execution. I'm more trying to understand whether example 1 or 2 more closely meets the criteria for normalization. The biggest difference between the two models is that the first has comments stored in one table and are dependent on a sourcetype/category, while the second has two standalone comments tables for different purposes.

Example 1 --Stores comments in one table
[CODE]
--Set as table variables for examples only

declare @Employee table (EmployeeId int primary key not null, Lname varchar(30), Fname varchar(30))
declare @Absence table (AbsenceId int primary key not null, AbsenceDate date, EmployeeId int, Category smallint, Commentid int)
declare @Incident table (IncidentId int primary key not null, IncidentDate date, EmployeeId int, Category smallint, Commentid int)
declare @Comments table (CommentId int primary key not null, SourceCategory smallint, CommentText varchar(max))

Insert into @Employee (EmployeeId, Lname, Fname)
values (1, 'Jones', 'Sarah'), (2, 'Williams', 'Jane')
Insert into @Absence (AbsenceId, AbsenceDate, EmployeeId, Category, Commentid)
values (1, '2011-01-01', 1, 10, 1), (2, '2011-01-02', 1, 10, 2)
Insert into @Incident (IncidentId, IncidentDate, EmployeeId, Category, Commentid)
values (1, '2011-03-01', 1, 20, 3), (2, '2011-03-30', 2, 20, 4)
Insert into @Comments (CommentId, SourceCategory, CommentText)
values (1, 10, 'first comment'), (2, 10, 'second comment'), (3, 20, 'third comment'), (4, 20, 'fourth comment')

SELECT e.EmployeeId, e.Fname, e.Lname, A.AbsenceId, A.AbsenceDate, AC.CommentText
FROM @Employee E
LEFT OUTER JOIN @Absence A ON A.EmployeeId=E.EmployeeId --NOTE A.Category=10
LEFT OUTER JOIN @Comments AC ON AC.CommentId=A.Commentid AND AC.SourceCategory=A.Category

union

SELECT e.EmployeeId, e.Fname, e.Lname, I.IncidentId, I.IncidentDate, ic.CommentText
FROM @Employee E
LEFT OUTER JOIN @Incident I ON I.EmployeeId=E.EmployeeId --NOTE I.Category=20
LEFT OUTER JOIN @Comments IC ON IC.CommentId=I.Commentid AND IC.SourceCategory=I.Category

ORDER BY 1[/CODE]

Example 2 --Stores comments in two separate tables

[CODE]
--Set as table variables for examples only

declare @Employee table (EmployeeId int primary key not null, Lname varchar(30), Fname varchar(30))
declare @Absence table (AbsenceId int primary key not null, AbsenceDate date, EmployeeId int, AbsenceCommentid int)
declare @Incident table (IncidentId int primary key not null, IncidentDate date, EmployeeId int, IncidentCommentid int)
declare @AbsenceComments table (AbsenceCommentId int primary key not null, CommentText varchar(max))
declare @IncidentComments table (IncidentCommentId int primary key not null, CommentText varchar(max))

Insert into @Employee (EmployeeId, Lname, Fname)
values (1, 'Jones', 'Sarah'), (2, 'Williams', 'Jane')
Insert into @Absence (AbsenceId, AbsenceDate, EmployeeId, AbsenceCommentid)
values (1, '2011-01-01', 1, 1), (2, '2011-01-02', 1, 2)
Insert into @Incident (IncidentId, IncidentDate, EmployeeId, IncidentCommentid)
values (1, '2011-03-01', 1, 1), (2, '2011-03-30', 2, 2)
Insert into @AbsenceComments (AbsenceCommentId, CommentText)
values (1, 'first Abs comment'), (2, 'second Abs comment')
Insert into @IncidentComments (IncidentCommentId, CommentText)
values (1, 'first Inc comment'), (2, 'second Inc comment')

SELECT e.EmployeeId, e.Fname, e.Lname, A.AbsenceId, A.AbsenceDate, AC.CommentText
FROM @Employee E
LEFT OUTER JOIN @Absence A ON A.EmployeeId=E.EmployeeId
LEFT OUTER JOIN @AbsenceComments AC ON AC.AbsenceCommentId=A.AbsenceCommentid

union

SELECT e.EmployeeId, e.Fname, e.Lname, I.IncidentId, I.IncidentDate, ic.CommentText
FROM @Employee E
LEFT OUTER JOIN @Incident I ON I.EmployeeId=E.EmployeeId
LEFT OUTER JOIN @IncidentComments IC ON IC.IncidentCommentId=I.IncidentCommentid

order by 1[/CODE]

Any suggestions will be greatly appreciated!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-17 : 23:22:51

Thinking about it with this in front of me all together, I'm thinking the second model is closer to meeting normalization criteria because the comments. The fact that a select query would be dependent on more than just the key (ie sourcecategory) would disqualify it from 1st normal form. Confusing because the application we use is modeled in this way. I would think the developers would try to normalize their database as much as possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 23:28:53

will you have more than 1 comments per absence ? If it is always one to one, why not have the commenttext in the absense table ?

KH
[spoiler]Time is always against us[/spoiler]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-17 : 23:34:19

Hi Khtan, theoretically, probably not in this particular situation. But there are some other situations with similar table structures that I would want to have multiple comments. For argument's sake, let's say that I did want to have multiple comments...which example would you suggest?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 23:55:09

in that case, it doesn't really make much different for either method. Personally, i will go for Example 2 as i will know information about the absence will be store in table name that begins with absence. I would not have to think twice what is in that general "comment" table.

KH
[spoiler]Time is always against us[/spoiler]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-18 : 00:05:45

Ok, cool. That was what I was thinking. I know I've struggled with the first configuration alot with the application I use. Especially when I'm trying to set up data integrity reports to make sure that supervisors are following up on issues (ie, looking for incidents/absences where there are null values on the supervisor's part). I've overcome that by setting join conditions based on the source, but seems a bit hokey. I think the larger question I have is to better understand how to stay on track with normalization.

I'm setting up another example that's similar but has a bit different structure and I'm really getting stuck with it! :)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-18 : 00:26:44

Ok so here's another two examples. This next series is sort of a replica of a feedback interface we created for our bus operators. The current model most closely resembles example 1. I've been wavering back and forth on moving to the second model. I'm concerned that the feedback table is going to get overrun with too many records to really manage. It's on Access and not on a server; just a network drive. (My question is more for SQL Development in general though and my own understanding; not the end user interface necessarily).

Example 1 --Example has all feedback types grouped into one table.

[CODE]

DECLARE @EMPLOYEE TABLE (EMPLOYEEID INT PRIMARY KEY NOT NULL, LASTNAME VARCHAR(30) NOT NULL, FIRSTNAME VARCHAR(30) NOT NULL, BADGENUM INT NOT NULL)
DECLARE @FACILITY TABLE (FACILITYID INT PRIMARY KEY NOT NULL, FACILITYNAME VARCHAR(10) NOT NULL)
DECLARE @ROUTE TABLE (ROUTEID INT PRIMARY KEY NOT NULL, ROUTENAME VARCHAR(10) NOT NULL, ROUTEORDER INT NOT NULL)
--//We do not register passengers, so there's really no way to have a "Passenger" table here
DECLARE @VEHICLE TABLE (VEHICLEID INT PRIMARY KEY NOT NULL, VEHICLENUMBER INT NOT NULL)

DECLARE @FACILITYISSUETYPES TABLE (FACILITYISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))
DECLARE @PASSENGERISSUETYPES TABLE (PASSENGERISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))
DECLARE @ROUTEISSUETYPES TABLE (ROUTEISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))
DECLARE @VEHICLEISSUETYPES TABLE (VEHICLEISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))

DECLARE @FACILITYSUBISSUETYPES TABLE (FACILITYSUBISSUETYPEID INT PRIMARY KEY NOT NULL, FACILITYISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))
DECLARE @PASSENGERSUBISSUETYPES TABLE (PASSENGERSUBISSUETYPEID INT PRIMARY KEY NOT NULL, PASSENGERISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))
DECLARE @ROUTESUBISSUETYPES TABLE (ROUTESUBISSUETYPEID INT PRIMARY KEY NOT NULL, ROUTEISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))
DECLARE @VEHICLESUBISSUETYPES TABLE (VEHICLESUBISSUETYPEID INT PRIMARY KEY NOT NULL, VEHICLEISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))

--//Employees can only enter 1 feedback at a time and only select 1 feedback type (ie facility, Route, Passenger or Vehicle) at a time along with only one subissue at a time
DECLARE @FEEDBACK TABLE (FEEDBACKID INT PRIMARY KEY NOT NULL, EMPLOYEEID INT NOT NULL, FACILITYID INT NULL, ROUTEID INT NULL, VEHICLEID INT NULL, FACILITYISSUETYPEID INT NULL, PASSENGERISSUETYPEID INT NULL, ROUTEISSUETYPEID INT NULL, VEHICLEISSUETYPEID INT NULL, FACILITYSUBISSUETYPEID INT NULL, PASSENGERSUBISSUETYPEID INT NULL, ROUTESUBISSUETYPEID INT NULL, VEHICLESUBISSUETYPEID INT NULL, COMMENTS VARCHAR(256) NOT NULL)[/CODE]

Example 2 --Feedback separated depending on type

[CODE]
DECLARE @EMPLOYEE TABLE (EMPLOYEEID INT PRIMARY KEY NOT NULL, LASTNAME VARCHAR(30) NOT NULL, FIRSTNAME VARCHAR(30) NOT NULL, BADGENUM INT NOT NULL)
DECLARE @FACILITY TABLE (FACILITYID INT PRIMARY KEY NOT NULL, FACILITYNAME VARCHAR(10) NOT NULL)
DECLARE @ROUTE TABLE (ROUTEID INT PRIMARY KEY NOT NULL, ROUTENAME VARCHAR(10) NOT NULL, ROUTEORDER INT NOT NULL)
DECLARE @VEHICLE TABLE (VEHICLEID INT PRIMARY KEY NOT NULL, VEHICLENUMBER INT NOT NULL)

DECLARE @FACILITYISSUETYPES TABLE (FACILITYISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))
DECLARE @PASSENGERISSUETYPES TABLE (PASSENGERISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))
DECLARE @ROUTEISSUETYPES TABLE (ROUTEISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))
DECLARE @VEHICLEISSUETYPES TABLE (VEHICLEISSUETYPEID INT PRIMARY KEY NOT NULL, ISSUE VARCHAR(30))

DECLARE @FACILITYSUBISSUETYPES TABLE (FACILITYSUBISSUETYPEID INT PRIMARY KEY NOT NULL, FACILITYISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))
DECLARE @PASSENGERSUBISSUETYPES TABLE (PASSENGERSUBISSUETYPEID INT PRIMARY KEY NOT NULL, PASSENGERISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))
DECLARE @ROUTESUBISSUETYPES TABLE (ROUTESUBISSUETYPEID INT PRIMARY KEY NOT NULL, ROUTEISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))
DECLARE @VEHICLESUBISSUETYPES TABLE (VEHICLESUBISSUETYPEID INT PRIMARY KEY NOT NULL, VEHICLEISSUETYPEID INT NOT NULL, SUBISSUE VARCHAR(30))

--//Employees can still only select one feedback, one issue/subissue at a time, but I'm thinking this design would be cleaner to manage
DECLARE @FACILITYFEEDBACK TABLE (FACILITYFEEDBACKID INT PRIMARY KEY NOT NULL, EMPLOYEEID INT NOT NULL, FACILITYISSUETYPEID INT NOT NULL, FACILITYSUBISSUETYPEID INT NOT NULL, COMMENTS VARCHAR(256))
DECLARE @PASSENGERFEEDBACK TABLE (PASSENGERFEEDBACKID INT PRIMARY KEY NOT NULL, EMPLOYEEID INT NOT NULL, PASSENGERISSUETYPEID INT NOT NULL, PASSENGERSUBISSUETYPEID INT NOT NULL, COMMENTS VARCHAR(256))
DECLARE @ROUTEFEEDBACK TABLE (ROUTEFEEDBACKID INT PRIMARY KEY NOT NULL, EMPLOYEEID INT NOT NULL, ROUTEISSUETYPEID INT NOT NULL, ROUTESUBISSUETYPEID INT NOT NULL, COMMENTS VARCHAR(256))
DECLARE @VEHICLEFEEDBACK TABLE (VEHICLEFEEDBACKID INT PRIMARY KEY NOT NULL, EMPLOYEEID INT NOT NULL, VEHICLEISSUETYPEID INT NOT NULL, VEHICLEISSUESUBTYPEID INT NOT NULL, COMMENTS VARCHAR(256))[/CODE]

I realize there's no sample data...if this isn't clear, I can definitely set it up. I thought it would be neater to submit just the tables. I'm only focusing on the difference between feedback being in either one table or in multiple.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources