Better Database design?

[ssv45324]

We have a lot of tables and a couple of those tables are COmpany, Users.(Users are connected to company tables). Now we want to display the time correctly. Since ours is an international application, we need to display the time according to the timezone. So is it a good design practise to add a timezone column(with the literal value of timezone like GMT - 5, etc) to the 'Company' table or is it a good practise to create a new table TimeZone and link the entries in the Company table to the TimeZone table?

ANy respsonses are appreciated.

 

[jbenson001]

Create a table of states and the corresponding time zone

 

[ssv45324]

Thanks for the response. Why do you think this is a better design than the first one that i had suggested? Not questioning your response, just wanted to understand better.

 

[philhege]

Some time zones bifurcate states (

http://www.worldtimezone.com/time-usa12.php).

Also, some states or regions don't play by the time zone rules.

It's probably best to link the time offset to the physical entity.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[jbenson001]

I use it that way for relational puposes. You can just link to the state table from your company table by stateID.

Why is it better? Well, you will be able to use this table to relate to any other table if you need to. Also, consider data repetition. If you company table has many rows, and lets say half of them had the same timezone, that data would be redundant. If you are linking by and ID of type int, you are saving storage. In general, just better DB design.


Jim

 

[vongrunt]

OK, what in case company is located for example in Michigan state, Central Time zone?

Or South pole, Antarctica perhaps?
(nah, they do GMT).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ssv45324]

Thanks for the responses.
jbenson, if i create a seperate table for states how can we handle the issue mentioned by philhege?(Some timezones bifurcate timezones).

 

[jbenson001]

Good point by philhege. I didn't realize that some states have more than 1 timezone. I guess in the apps I worked on they didn't place that much importance on it. (I didn't design the tables).
Fixing it would now get trickier. I guess you would have to go down to a city level but that would be crazy. I guess in your case adding timezone to your company table might be the way to go. I am not sure of an exact answer for you. Maybe one of the gurus here can be of more help.. sorry...

Jim

 

[avanderlaan]

ssv45324,

Normally my tables have a column reflecting the "current" TZ offset corresponding to the time that the data was collected (since the offset can change with daylight savings time, etc.)

 

[SQLBill]

How are you going to handle Daylight Savings Time? Some states change and some don't.

One solution: Create a column for the time zone information in the original table (don't use GMT -5, etc. Just use -5, GMT is taken for granted). Then create a State_DST table that lists the states and Yes or No for if they use DST. When you query, link the two tables and if the current date is during DST, it will match the state and subtract one hour if the value is YES in the State_DST table. Then when a state changes from no to yes, you don't have to change it everywhere in the original table, just change it in the State_DST table.

-SQLBill

Posting advice: FAQ481-4875

 

[pwilson]

FYI: we dont have daylight savings where I live ...

along the daylight saving thing what happens in the time that that occurs.

Say that 2Am (or whenever daylight saving starts) you make some records

4-1-2004 2.00.01 am
4-1-2004 2.01.01 am
4-1-2004 2.02.01 am
4-1-2004 2.03.01 am

then at the end of the hour the time goes back to 2am and you make some more records. will they intersperced between the "old" records?

and then when the month comes along where you skip the 2 oclock hour, will SQL allow you to insert records in that hour if the server is set to some time with daylight savings.

 

[gmmastros]

For what it's worth...

My suggestion is to ignore the problem.

Your database should store all times as though it were GMT time. It should also return data that way.

Sounds simple, right? But it doesn't solve your problem, does it?

I can't claim to be familiar with all programming languages, but I know with VB, you can query the operating system to get time zone information. Once you get that time zone information, you can add or subtract hours accordingly.

You'll also have to adjust the time when inserting or updating data. Again, the time adjustment should be done client side, so that SQL Server only deals with UTC/GMT time.

This is just a thought, and probably how I would approach the problem. Whatever you decide to do, I wish you luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Good point George

 

[avanderlaan]

George,

You can only query the server for it's local timezone. If clients are distributed across timezones (ex. say oil rigs connecting to an oil company home site), you pretty much have to capture local TZ offsets so that events can be displayed in "local" time. This is particularly important in things like accident reconstruction, etc.

Normally we store both the sample time in UTC and the local TZ offset so that we can reconstruct the local time for display purposes.