January 31, 2003 at 2:37 am
Hi
I want to design a timesheet database. I want to have 2 fields in the Task table - TimeFrom and TimeTo. I would like these to be in increments of 15 minutes i.e. 8.00, 8.15, 8.30 etc.
Would I (could I) do this in SQL server 7 or would it be something that the VB front end would do?
Thanks
January 31, 2003 at 4:40 am
Normally I would handle application side. But as a safety you might want to use a Check Constraint to be sure an unforseen issue doesn't allow around the app. Here is an example of a table with a check for minute in 2 different field to be sure they are either 00, 15, 30 or 45.
CREATE TABLE [tbl_Time] (
[idx] [int] IDENTITY (1, 1) NOT NULL ,
[TimeFrom] [datetime] NOT NULL ,
[TimeTo] [datetime] NOT NULL ,
CONSTRAINT [PK_tbl_Time] PRIMARY KEY CLUSTERED
(
[idx]
) ON [PRIMARY] ,
CONSTRAINT [CK_tbl_Time_15MinIncr] CHECK ((datepart(minute,[TimeFrom]) = 45 or (datepart(minute,[TimeFrom]) = 30 or (datepart(minute,[TimeFrom]) = 15 or datepart(minute,[TimeFrom]) = 0))) and (datepart(minute,[TimeTo]) = 45 or (datepart(minute,[TimeTo]) = 30 or (datepart(minute,[TimeTo]) = 15 or datepart(minute,[TimeTo]) = 0))))
) ON [PRIMARY]
GO
Altough this will prevent you have to capture the error and know what to tell the user. Also the reason you should do in the app anyway is to avoid trips to the server that will fail for issues you want to prevent. Doing this in addition gives you a way to handle in addition for extra safety.
Edited by - antares686 on 01/31/2003 04:41:28 AM
January 31, 2003 at 6:12 am
Right, thanks Antares.
But, being a beginner, this has raised another question:
This was my original table
tbl_task
taskID (PK)
PersonID (FK)
Task
TimeFrom
TimeTo
Taskdate
So could I redo this table to look like this:
CREATE TABLE [tbl_Task] (
[idx] [int] IDENTITY (1, 1) NOT NULL ,
[TimeFrom] [datetime] NOT NULL ,
[TimeTo] [datetime] NOT NULL ,
[TaskID] [int] NOT NULL ,
[Task] [varchar] NOT NULL ,
[PersonID] [int] NOT NULL ,
[TaskDate] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_tbl_Task] PRIMARY KEY CLUSTERED
(
[idx]
) ON [PRIMARY] ,
CONSTRAINT [CK_tbl_Time_15MinIncr] CHECK ((datepart(minute,[TimeFrom]) = 45 or (datepart(minute,[TimeFrom]) = 30 or (datepart(minute,[TimeFrom]) = 15 or datepart(minute,[TimeFrom]) = 0))) and (datepart(minute,[TimeTo]) = 45 or (datepart(minute,[TimeTo]) = 30 or (datepart(minute,[TimeTo]) = 15 or datepart(minute,[TimeTo]) = 0))))
) ON [PRIMARY]
GO
So now idx is my primary key and it automatically generates number?
Also, another question - as you write the column names and data types enclosed in [] if I was to have - task varchar(50) - how would that be written?
Thanks again 🙂
January 31, 2003 at 11:05 am
You may want to use a more descriptive value instead of idx, something like TimeID may be better so that you associate with your table. You will need this to be able to have a PK as you probably noticed that taskID could repeat. However you could avoid that and use a combo PK of
taskID, TimeFrom
but I would do the TimeID in this situation.
As for the [] question it is good to use since will qualify a column name. Mostly though, besides SQLs choice to display and use that way, it is to handle bad practices like using reserved keywords like ID, TABLE and such or odd charcters like - or names with spaces. Not required otherwise.
February 5, 2003 at 4:50 am
OK great.
As for my next table - Person
PersonID - PK
Firstname
Lastname
Department
Would it be advisable to make PersonID an IDENTITY column also?
There will be about 10 people using this database but I don't want the engineers to have to log on using an ID (they'll forget it!). I want them to log on with their name and department.
Or should I assign the PersonID myself i.e. If firstname = 'John' then Insert INTO PersonID value '001'.
February 5, 2003 at 5:20 am
My only concern is that they could potentially have the same name and department.
Ex.
John A Smith IS
John D Smith IS
but you are only handling First and Last name. So you open yourself to future potential issues.
Also what if they were
John Albert Smith IS
John Adam Smith IS
If you use only middle initial then you run the risk of same possiblity.
I would say if you are concerned with ID then use either their Employee ID for the company and use it as the PK or User ID they use for logging into other servers (if you go this route then it makes it easier on the user and safer for you but consider possible issues that could arise). Or use a created ID (IDENTITY value) and have them learn it. Sure they may forget, but you have piece of mind for future personnel coming and going.
The idea of an assigned ID is good also.
February 6, 2003 at 8:22 am
I'd suggest that the PK on your person table be the user's Windows login name. Not too many people forget that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply