September 1, 2004 at 2:04 pm
I have finished the design of a database. The database will have two ASP .NET systems that use the data. One system will be used by internal staff via the Intranet for maintaining the data (Admin System). And one system will be used by the public via the Internet to read the data (Public System).
The records that will be available to be read via the Internet will be based on a date and a time as compared to the current date. For instance, I will only see a particular record on a certain web page if it is before a date and a certain time. Otherwise it will display somewhere else or not at all. The Internet system will display the time in parenthesis next to the date as in 09/01/2004 (2:00 PM).
The Admin System will give the user two fields to enter the information. One for the date portion and one for the time format. The time is required to default to 2:00 PM but can be changed by the user of the Admin system. There must be both a date and a time. Both components are critical to what the public will see. We also will be displaying information to the user via reports based on filtering on the date and the time.
My question is given the above requirements, am I better splitting the date and time into two separate fields or having them go into one field? After talking to the developers, it seems that it doesn't make a difference from the programming end as you have to parse out the date and time in both systems in either database design scenario. However, from my standpoint, I can increase the integrity of the data by making the time field required and also making the date field required. And I think it makes the importance of the time field more visible if it's in its own field.
Any thoughts on this from you database design gurus out there as to which is the more correct or accepted method of design? Generally, for audit type fields where the date isn't an important element in an application, I just use one field to store the date and time.
Thanks in advance.
Sue
September 1, 2004 at 2:24 pm
I think it's a wash as well, but you could argue that they do go in two different fields because you want to require them. The time wouldn't be required if they are in one field, although it would always be there.
September 1, 2004 at 9:52 pm
Another consideration, although a small one, is the addition of another field that must be maintained and the space that extra field takes.
One datetime field is 8 bytes, 2 is 16 bytes. The difference in performance probably wouldn't be measurable until you got into the 100,000's of records, but it would make a difference.
Unless you need dates prior to 1/1/1900 or need to store time with .003 seconds accuracy, you can save space using the smalldatetime datatype. It takes only 4 bytes and still stores both date and time.
For example, if every "page" of data (roughly 8K) was completely full, which it normally isn't, but we'll assume it is, you wouldn't see any difference saving 8 Bytes of space until you get to 1000 records, then it takes 1 additional page to store all your records. At 10000 records, it takes 10 additional pages and so on. It's not so much the space as it is the number of pages SQL Server has to leaf through to get to your record. The smaller your row, the more rows that can fit on a single data page, the faster SQL Server can find the row you want.
It's pretty easy to manipulate the dates and you can put a constraint limiting the time values to non-zero values.
(datepart(hour,[TimeField]) > 0 and datepart(minute,[TimeField]) > 0 and datepart(second,[TimeField]) > 0 and datepart(millisecond,[TimeField]) = 0)
September 2, 2004 at 1:30 am
Stick with one datetime data typed column. Your GUI / Client Program should be responsible for enforcing that a date and time be entered. Just turn off NULLS in the column to make sure that sometype of date/time was inserted.
Also, storing them in the one field means you can do on the fly manipulation of the dates, like dateadd, datediff, datepart, and you can sort and select based on ranges, and date comparisons (like where dateA > dateB).
You put numbers in INT and DECIMAL columns, why not put a date in a DATETIME?
Julian Kuiters
juliankuiters.id.au
September 2, 2004 at 1:37 am
I would tend to keep date and time together. If you can get away with a SMALLDATETIME field then do so.
If you do decide to store date and time separate then, provided that you don't want to do calculations on the time I would tend to store it in a SMALLINT field, after all values are only going to range from 0000 to 2359.
If all you want to do is store an hour then use TINYINT.
September 2, 2004 at 6:50 am
Thank you to you all for your advice.
This is not a big transactional system, so the space is not a big concern.
But based on your comments I'm going to change it to one field and rename the field to add the word "Time" to the end. The Admin system will have to enforce that the time portion is greater than 0 (would have had that validation in place anyway).
Have a great day!
Sue
September 2, 2004 at 7:14 am
Use a single NOT NULL date field for both, and add a check constraint to make sure the time is also present.
ALTER TABLE tbl ADD CONSTRAINT chktime CHECK (DATEPART(hour, datefield) > 0 OR DATEPART(minute, datefield) > 0)
September 2, 2004 at 7:25 am
Thanks Scott. I will do that. I always like to have an extra layer of protection on the database to enforce important business rules. We are devoted to strong security. . . .but I like to know that even if somebody gains direct access to the tables, they can't violate some of the rules.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply