Normalizing Dates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jon Winer » Normalizing Dates

16 posts, Page 2 of 2

««1 2

Normalizing Dates

Rate Topic

Display Mode

Topic Options

Author

Message

cs_troyk

Posted Monday, September 08, 2003 12:35 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 5:22 PM
Points: 1,205, Visits: 684

quote:

Hi Troy,

Not sure I follow you in regards to incorrect data within the date variables. Could you elaborate?

Hi Jon;

Here's a simplified script that demonstrates the problem I see:

-------- BEGIN SQL SCRIPT --------
SET NOCOUNT ON

-- *********** Original version ************
CREATE TABLE dbo.TimeTable_1(
TimeID int NOT NULL PRIMARY KEY, -- A guess on the PK
TheDate datetime NOT NULL,
-- Dependent columns, but defined independently:
-- erroneous data can be inserted...
TheDay varchar(20),
TheMonth varchar(20),
TheYear char(4)
)
GO

CREATE PROC dbo.UpdateTimeTable_1
AS
UPDATE dbo.TimeTable_1
SET TheDay = DATENAME(weekday,TheDate),
TheMonth = DATENAME(month,TheDate),
TheYear = YEAR(TheDate)
GO

INSERT INTO dbo.TimeTable_1(TimeID, TheDate)
SELECT 1, getdate() UNION ALL
SELECT 2, getdate()
GO

-- Check data
SELECT TheDate, TheDay, TheMonth, TheYear FROM TimeTable_1

-- Need an extra call to the sproc to get data corrected...
EXEC dbo.UpdateTimeTable_1

-- Check data
SELECT TheDate, TheDay, TheMonth, TheYear FROM TimeTable_1

-- A rogue update: nothing is preventing this...
UPDATE TimeTable_1
SET TheMonth = 'June' WHERE TimeID = 1

-- A more blatant example
UPDATE TimeTable_1
SET TheMonth = 'FooBar' WHERE TimeID = 2
GO

-- Check data
SELECT TheDate, TheDay, TheMonth, TheYear FROM TimeTable_1
GO
-- Clean up test box
DROP PROC UpdateTimeTable_1
DROP TABLE TimeTable_1
GO

-- *********** Now, try with computed columns ************
CREATE TABLE dbo.TimeTable_2(
TimeID int NOT NULL PRIMARY KEY,
TheDate datetime NOT NULL,
-- Computed columns: no incorrect data allowed
TheDay AS DATENAME(weekday,TheDate),
TheMonth AS DATENAME(month,TheDate),
TheYear AS YEAR(TheDate)
)
GO

INSERT INTO dbo.TimeTable_2(TimeID, TheDate)
SELECT 1, getdate() UNION ALL
SELECT 2, getdate()
GO

-- Check data
SELECT TheDate, TheDay, TheMonth, TheYear FROM TimeTable_2

-- Try rogue update with TimeTable_2: DBMS prevents the bad data...
UPDATE TimeTable_2
SET TheMonth = 'FooBar' WHERE TimeID = 2
GO

SELECT TheDate, TheDay, TheMonth, TheYear FROM TimeTable_2
GO

-- Clean up test box
DROP TABLE TimeTable_2
GO
-------- END SQL SCRIPT --------

How do you prevent the possibility of users introducing data errors in the TimeTable_1 sample from the above script?

TroyK

Post #77267

jwiner

Posted Monday, September 08, 2003 1:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 6:58 AM
Points: 115, Visits: 17

I think I understand where you are going...

The only way anything can be written to the production TimeTable is via the stored procedure UpdateTimeTable. No applications or users have write permissions to this table. There is only execute permissions on the stored procedure. This stored procedure gets called from a SQL job that is scheduled to run daily. The stored procedure is never reference anywhere in the web application.

So, the short of it, is that no one except the DBA or myself has rights to the sql server to run this stored procedure. There is really no way for any 'bad' data to get into the table unless the dba or myself were to intentionally put it in there.

Post #77268

cs_troyk

Posted Monday, September 08, 2003 3:44 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 5:22 PM
Points: 1,205, Visits: 684

quote:

I think I understand where you are going...

The only way anything can be written to the production TimeTable is via the stored procedure UpdateTimeTable. No applications or users have write permissions to this table. There is only execute permissions on the stored procedure. This stored procedure gets called from a SQL job that is scheduled to run daily. The stored procedure is never reference anywhere in the web application.

So, the short of it, is that no one except the DBA or myself has rights to the sql server to run this stored procedure. There is really no way for any 'bad' data to get into the table unless the dba or myself were to intentionally put it in there.

But why define a table that even has the possibility of bad data in the first place?

Would you define two tables that have a FK-to-PK relationship, but not explicitely declare the relationship, hoping that all applications programmed against them correctly enforce the data integrity externally?

These are the kinds of problems that the Relational Model was invented to solve.

Do you have other exceptions like this in your system? Are they all documented? What happens when you or the DBA leave the company?

Here's an article by Joe Celko that goes into more detail:
http://www.intelligententerprise.com/030531/609celko1_1.shtml?/database

If that link won't work, try googling for "Celko" and "Logic of Failure".

TroyK

Post #77269

jwiner

Posted Tuesday, September 09, 2003 6:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 6:58 AM
Points: 115, Visits: 17

Troy,

I'm not sure we're are going to be able to come to an agreement on what is wrong with the table and stored procedure (I don't see an issue)...

You're view that bad data can get introduced to the table as an extremely remote possibility is true of any table. As to your relationship (primary key/foreign key) question: The timetable has an auto identity column used as the primary key. Any tables that reference the timetable with foreign keys do so using the identity column from the timetable. The relationships are explicit, foreign key constraints exist throughout the database, including the timetable.

I read the article you pointed me to, but I don't see how it applies. Referential integrity is enforced in the my DB, not the application.

Does anyone else care to chime in to help me clarify what Troy is trying to get at?

Post #77270

Andy Warren

Posted Tuesday, September 09, 2003 9:34 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

I'd be interested to hear other points of view on this as well.

Some thoughts:

- Im not opposed to using an indexed view or computed column, it's often a good way to maintain extra data without the hassle of triggers.

- I agree with Jon that since all access is gated through the stored proc and given that the proc is documented, I don't see his approach as being dangerous or hard to maintain. After all, this table does represent a good sized chunk of what he is doing, not a minor piece that might be overlooked.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #77271

Ian Yates

Posted Wednesday, December 15, 2004 9:11 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

And I bet the mechanics of having the auto-inc number generated within a view would make it rather inefficient - i assume your other tables store the identity value and not the datetime values themselves. AND, if you did decide to change things slightly down the track, you *might* get more or less values and hence stuff up your numbering and foreign keys anyway.

As for the daylight savings issue - I suppose it really depends on your company and application - UTC time is great but as someone pointed out, you cannot meaningfully (easily) do things like plot help centre calls throughout the day on UTC time without doing the time shift +/- 1hr for various dates of the year. YUK

I really like the idea - I have a medical records recalls system that has to send letters to patients to remind them to come back for their followup procedure / consultation several weeks, months, years after their initial visit. Such a table would make it easier(?) to "add 9 months" and not worry about wrapping days of the month around, etc.

Post #151316

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions