Database design help

Bakedgoods · 12 Sep 2007 at 09:01

I'm struggling to come up with a solution as my programming experience is limited. Basically i'm looking to create a program which will allow people to log the number of hours they have spent on a project, for each day of the week. They may spend time on several different projects each week.

I can't decide how to store the date.

tbl_Hours
ID
projectID
projectName
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

tbl_Projects
projectID
projectName
projectDescription
projectOwner

Could this be done better by just using a Day field instead? And then somehow encorporating a date into it? I was also thinking of maybe having a WeekStart field, which would just store the date of the start of the week for each new week.

If i'm not being clear please do ask me to clarify.

I'll be creating the program using ASP.net 2.0 and SQL Server for the database.

Any help really is appreciated

Beansprout · 12 Sep 2007 at 09:17

Whoa

Hours
ProjectID
Date
Hours

Projects
ID
Name
Description
Owner

Databases handle dates pretty well - eg in mysql use a DATETIME field for the date up there

Edit: Or just a DATE

rpstewart · 12 Sep 2007 at 09:21

The hours table is all wrong. There's no need for the project ID and name since they're both in the projects table so lose the name column from the hours table. There's no point in having seven day named fields either, replace that with a date field and an hours field for the value. I assume the ID field is meant to represent the person, if so this needs a less ambiguous name.

You haven't specified primary keys for either table although the project ID implies this for the projects table, on the hours table the key should be a combination of the person's ID, the date and the project ID.

Bakedgoods · 12 Sep 2007 at 09:30

rpstewart, I need to store what project the user has worked on, so it needs to be in the Hours table...

The projects table is for storing all the project information. The user then must be able to select a project from this list when inputting their hours.

I think my database design looks all funky because I was trying to do it with using a ASP.net GridView in mind, because it would display exactly how I want it.

For example....

ID----DATE-----------Project----Monday----Tuesday----Wednesday----Thursday----Friday
1-----10/07/2007-----000001------5-----------3------------5------------3-----------7--
2-----10/07/2007-----000004------5-----------4------------2------------5-----------7--
3-----11/07/2007-----000001------5-----------3------------5------------6-----------7--

Cheers for helping guys I do appreciate it. Does that make it any clearer or?

Beansprout · 12 Sep 2007 at 09:47

Wardie said:
rpstewart, I need to store what project the user has worked on, so it needs to be in the Hours table...

So you just need one way to uniquely identify the project - the project ID.

One minor error in my post above, no user ID in the hours table, oops

Bakedgoods · 12 Sep 2007 at 10:01

The reason I include both fields is because I was hoping to somehow combine them into a single drop down menu/list to that it's easier for the user to select their project. It's easier to recognise a name than a number and helps to make sure they don't get it wrong.

You're right though I can just SELECT that from the projects table but I only need to store the projectID. Cheers

I'm struggling to work out how i'll be able to present the data in the above fashion, i'd like it to be editable in that format too.

ARghhhhhh

KevinCB · 12 Sep 2007 at 11:19

Are you still working with ASP.NET?

If so, you can create a drop down with the ID and Name using a select query.
The ID will be hidden, while the Name is displayed in the drop down.

Bakedgoods · 12 Sep 2007 at 11:22

KevinCB said:
Are you still working with ASP.NET?

If so, you can create a drop down with the ID and Name using a select query.
The ID will be hidden, while the Name is displayed in the drop down.

Yes I am Kevin (you're a helpfully bugger). I'd like to display both of them next to each other really.

My only problem is working out how i'm going to display the week like I shown above.

PAz · 12 Sep 2007 at 18:59

If you're developing in ASP.net probably best to avoid using "DATE" as a db field name

Bakedgoods · 13 Sep 2007 at 08:30

Cheers for the heads up PAz.

So has anyone got any idea how I can get it to display as above? Remember there can be multiple tasks entered for each day.

PAz · 13 Sep 2007 at 08:46

Beansprouts suggestion is more logical then you have posted above to be honest. You're output would look more like this:

ID-----DATEWORKED-------Project------Hours
004---01/02/07-------------00078--------4
004---01/02/07-------------00138--------2
011---02/02/07-------------00078--------1

Which is more sensible and will help minimise null data (not that this is really an issue assuming one user can work on more than one project per day).

If you wanted to have a nice output like you posted, you could probably achieve that through the .NET front-end. I am sure one of my colleagues did something similar, whereby she used the date to query a calender which then returned the actual day name (mon/tues/wed etc) based on the actual date. I haven't done it myself, it's just I sit near the .NET developers at work and sometimes nose into what they are doing (it's only a "hobby" of mine).

Bakedgoods · 13 Sep 2007 at 11:06

Now I can't even work out how to get in to insert when using a uniqueidentifier for ID fields.

Trying to setup a page to insert projects using a DetailsView control. Obviously when inserting the ProjectID field should be hidden and should auto-generate, how do I go about doing this?

If I just remove the field from the DetailsView and the INSERT query then it shouts at me because uniqueidentifiers can not contain null values. How do I get it to automatically generate this and hide it from the user?

Please help

Thanks for the help so far guys you've been great

DuncanP · 13 Sep 2007 at 11:46

As far as I can see you have a many to many relationship. EG many people can work on 1 project and 1 person can work on many projects. The normal answer to this problem is to create a new xref table to join the other 2.

PROJECT
ProjectID (Unique Primary Key)
Name .....etc

RERSON
PersonID (Unique Primary Key)
Name .... etc

PERSONPROJECT
ID (Unique Primary Key - can use Identity)
ProjectID
PersonID
Date
Hours

This record would be produced when the person booked time using the system

This allows you to easily get stats on person or project

SELECT COUNT(Hours)
WHERE PersonID = @PersonID
AND DATE BETWEEN @StartDate and @EndDate

or

SELECT COUNT(Hours)
WHERE ProjecID = @ID
AND DATE BETWEEN @StartDate and @EndDate

where @xxx are parameters passed to the stored procedure.
You can do a join to the appropriate table to get other details.

This is the normalised way of doing things.

DuncanP · 13 Sep 2007 at 11:50

Sorry COUNT should be SUM, if you want the total

so
SELECT SUM(Hours) FROM PersonProject
WHERE ProjecID = @ID
AND DATE BETWEEN @StartDate and @EndDate

and
SELECT SUM(Hours) FROM PersonProject
WHERE PersonID = @ID
AND DATE BETWEEN @StartDate and @EndDate

Bakedgoods · 13 Sep 2007 at 11:54

Cheers Duncan that;s pretty much what i've got now anyways from the above posts

Read my last post for where i'm stuck now ha-ha.

DuncanP · 13 Sep 2007 at 12:16

Combo1 populated with Select PersonName, PersonID from Persons
Combo2 populated with Select ProjectName, ProjectID from Projects
Textbox for HoursWorked
The date can be entered or can default to today with SQL GetDate

INSERT ProjectPeople
(PersonID, ProjectID, HoursWorked, TheDate)
VALUES
(PersonID, ProjectID, Hours, GetDate)

As the PK is an identity column it is autogenerated and does not have to be specified.

In the select you can use DayNo to work out days of the week.
to to work out the days of the week using GROUP BY

SELECTDayNo, SUM(Hours), from PERSONPROJECTS
GROUP BY DAYNO
WHERE personID = @PersonID
AND DATE between @STARTDATE ans @EndDate

This will give
1, 234.23
2, 2345.23
3, 545645
etc

I think that SQL 2005 has a pivot function which will laaow the above data to be put into a single record.

Hope that this helps

Bakedgoods · 13 Sep 2007 at 12:36

When i've tried to insert pretty much using that query but using a DetailsView control it's telling me that projectID can not be null.

Can I manipulate the GridView to allow me to EDIT/DELETE records even with ComboBox's added etc?

Or am I going to have to learn about editing/deleting files and forget it?

DuncanP · 13 Sep 2007 at 13:12

As far as I can remember the Datagridview doe not allow the addition or update of records in bound mode. I spent days writing my own control based on the datagridview only to find that updates are not allowed (I was not happy!!!!).

The answer is to write the update with an ado command and then refresh the grid. An example is shown below( my data and field names etc)

++++++++++

Update the table (from an edit screen)

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim strProcname As String
If Not Validate_Data() Then
Exit Sub
End If
Try
Dim cnn As New SqlClient.SqlConnection(Get_Connection_String())
cnn.Open()
If strMode = "ADD" Then
strProcname = "sp_Country_Insert_dwp"
Else
strProcname = "sp_Country_Update_dwp"
End If
Dim cmd As New SqlClient.SqlCommand(strProcname, cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim SQLparam As SqlClient.SqlParameter = cmd.Parameters.Add("@Country_ISO", SqlDbType.NChar)
SQLparam.Value = txtISO.Text
SQLparam = cmd.Parameters.Add("@Country_Name", SqlDbType.NVarChar)
SQLparam.Value = txtName.Text.Trim
SQLparam = cmd.Parameters.Add("@Country_In_Eu", SqlDbType.Bit)
SQLparam.Value = chkEUMember.Checked
SQLparam = cmd.Parameters.Add("@Country_Currency_ISO", SqlDbType.NChar)
SQLparam.Value = cboh.Selected_Value
SQLparam = cmd.Parameters.Add("@Country_In_Use", SqlDbType.Bit)
SQLparam.Value = chkInUse.Checked
cmd.ExecuteNonQuery()
blnChanged = True
Me.Close()
Catch ex As System.Exception
Error_Message(ex.Message)
End Try

End Sub
+++++++++
REFRESH THE DataGridView

Me.Sp_Select_Country_Currency_dwpTableAdapter.Fill(Me.Parallel_ImportDataSet.sp_Select_Country_Currency_dwp)

It seems odd that you have to use separate mechanisms for displaying and updating the data but you are using a disconnected recordset to populate the grid and it does not know that the recordset has changed.

Hope that this helps

Bakedgoods · 13 Sep 2007 at 13:20

Well i've seen methods to allow the GridView to add data relatively simply, and by standard is allows editing.

What do you mean by bound mode?

Bakedgoods · 13 Sep 2007 at 13:59

I can't even get it to insert just using the DetailsView. Here's the query...

Here's the error:

Code:

Cannot insert the value NULL into column 'ProjectID', table 'Training.lward.Projects'; column does not allow nulls. INSERT fails.
The statement has been terminated. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ProjectID', table 'Training.lward.Projects'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Here's the code:

Code:

SELECT [ProjectID], [ProjectName], [ProjectDescription], [ProjectOwner] FROM [Projects]

Code:

INSERT INTO [Projects] ([ProjectName], [ProjectDescription], [ProjectOwner]) VALUES (@ProjectName, @ProjectDescription, @ProjectOwner)

Database design help

Capodecina

Capodecina

Capodecina

Capodecina

Capodecina

Capodecina

Capodecina

Capodecina

Capodecina

Capodecina