April 15, 2011 at 12:43 pm
I received this table design from a "friend" and I would like to make some suggestions on normalizing it:
CREATE TABLE dbo.transcript
(
recordID INT, -- assume this is an identity PK
courseID VARCHAR(20),
ApplicationID VARCHAR(256),
userID UNIQUEIDENTIFIER,
creationDate SMALLDATETIME,
bookmark VARCHAR(64),
courseStartDate SMALLDATETIME,
courseEndDate SMALLDATETIME,
lastAccessDate SMALLDATETIME,
lesson1StartDate SMALLDATETIME,
lesson1CompDate SMALLDATETIME,
lesson2StartDate SMALLDATETIME,
lesson2CompDate SMALLDATETIME,
lesson3StartDate SMALLDATETIME,
lesson3CompDate SMALLDATETIME,
lesson4StartDate SMALLDATETIME,
lesson4CompDate SMALLDATETIME,
lesson5StartDate SMALLDATETIME,
lesson5CompDate SMALLDATETIME,
masterTestStartDate SMALLDATETIME,
masterTestCompDate11 SMALLDATETIME,
masterTestCompDate12 SMALLDATETIME,
masterTestCompDate13 SMALLDATETIME,
masterTestCompDate14 SMALLDATETIME,
score11 SMALLINT,
score12 SMALLINT,
score13 SMALLINT,
score14 SMALLINT,
courseStatus VARCHAR(256),
deleted BIT,
ackFormDate SMALLDATETIME,
ackName VARCHAR(64),
ackEmail VARCHAR(64),
ackID VARCHAR(50)
)
Essentially this is for an online training application for a specific course that will have to be repeated every year. At this time the course consists of 5 lessons, I don't know if that will ever change, but I always think that it could. A person does not have to take the entire course or even lesson in one sitting so the bookmark column will be used to mark the most recent stopping point so the training can restart from there. There is a test at the end of the course and historical scores must be maintained.
I have an idea of how I'd do it, but I'd love to have the real data architects our there come up with a design. Assume that there is a user table out there with user name and password.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 12:50 pm
Break it out into normal entities:
User
Course
CourseLession
CourseTest
UserCourseLession
UserCourseTest
April 23, 2011 at 2:13 pm
This table is in 1st Normal Form.
Is you see something like Lession1, Lession2, or Order1, Order2, Order3, the database is in first normal form.
Then you break the take down to 2nd Normal Form into it each individual entity.
Then you look at your structure and make sure the you have broken down each entity do that you do not have broken the table down into the proper entities.
For example you should store the Product_ID in the Orders Table. The Product_ID relates to the Products table via the Product ID.
You should not store both the Product_ID and Product_Name in both the Orders_Detail and Product Table.
If you have additional information let me know but you might want to goggle Relational Database Design.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 23, 2011 at 2:23 pm
You may want to refer to the following article concerning the topic of normalization:
http://db.grussell.org/section009.html
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply