Database

25-Jun-07 6:20

I am building an application similar to a help desk application. This application allows the management to assign and track all assigned tasks. The users will be a create a trouble ticket and track and update assigned tasks.

Below is the current ERD:
DEPT(DEPT_SID, DEPT_NAME)
TASK(TASK_SID, TASK_DESC)
PRIORITY(PRIRITY_SID, PRIORITY_LEVEL)
USERS(USERS_SID, USERNAME, LAST_NAME, FIRST_NAME)
STAFF(STAFF_SID, STAFF_USERNAME)
ASSIGNMENT(A_SID, STAFF_SID, PRIORITY_SID, TASK_SID, DEPT_SID, USERS_SID, A_TITLE, A_DESC, A_DATE)
ASSIGN_LOG(A_LOG_SID, STATUS_SID, A_SID, A_LOG_DATE, A_LOG_NOTES, RESOLVE_DATE)
STATUS(STATUS_SID, STATUS_DESC)

Question:
1) How do I change the design to I only use one table for "Create By:" and "Assign To:"?

Comments and design suggestions are welcome.

Thanks,

Steve C.
kstv@netzero.net

Colin Angus Mackay25-Jun-07 6:48

25-Jun-07 6:48

astv wrote:
Below is the current ERD:

That isn't an ERD. It is a bunch of text. I can only assume (because you haven't said) that the first bit of text represents a table name and everything in the brackets represents the columns. Perhaps the bolded text are primary keys (but who, other than you, can really know). Also a completely wilde guess suggests that the underlined columns might be foreign keys).

If you are going to describe the table structure don't assume people will understand your structure unless you tell them what it means.

astv wrote:
1) How do I change the design to I only use one table for "Create By:" and "Assign To:"?

No where in your structure is "Created By:" or "Assign To:". What does this mean?

astv wrote:
Comments and design suggestions are welcome.

I can't. I don't understand what you have, so I can't offer suggestions on how to get where you are going.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Ready to Give up - Your help will be much appreciated.

My website

astv25-Jun-07 8:58

25-Jun-07 8:58

My apologies. What I have posted are the tables and fields. I followed how my book on how to describe the tables and fields. I tried to paste an image of my ERD but I could not figure it out.

[Table Name]([PK], [FK], [Other Fields])

The fields "Created By" and "Assigned To" are a part of the ASSIGNMENT table. Again I apologize, I missed this two fields. Since, "Assigned To" can have multiple values, this may have to be normalized. This is the piece that is unclear to me.

Another is, if a task is reassigned to a different user, I may want to know who reassigned this specific task.

Thanks for your response.

Steve C.
kstv@netzero.net

Colin Angus Mackay25-Jun-07 9:34

25-Jun-07 9:34

astv wrote:
this may have to be normalized

A guide on normalising the datamodel[^]

Colin Angus Mackay25-Jun-07 9:45

25-Jun-07 9:45

So your assignment table actually looks more like this:

A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo

astv wrote:
How do I change the design to I only use one table for "Create By:" and "Assign To:"?

You also mention that you want to record if a person reassigns a task. I would guess that should go in the ASSIGN_LOG table. You may want to add a couple of nullable columns for that. You might also want to keep the current assignee in the ASSIGNMENT table for performance reasons.

Also, I don't know what you mean by "only use one table for "Create By:" and "Assign To:". They already are in one table.

astv25-Jun-07 10:25

25-Jun-07 10:25

Yes the assignment table looks exactly like how you have it.

Ok, if I understand you correctly, when an entry is made in the ASSIGNMENT table, an entry is also made in the ASSIGN_LOG table. This would make sense since my intent for the ASSIGN_LOG table is to keep assignment historicals.

as for The use of single table for "CreatedBy" and "AssignTo" question:

Currently, in my design I use the USERS table to populate the CreatedBy field and the STAFF table to populate the AssignTo field.

USERS
USERS_SID [PK]
USERNAME
LAST_NAME
FIRST_NAME

STAFF
STAFF_SID [PK]
STAFF_USERNAME

ASSIGNMENT
A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo

Is this ideal to use two different tables storing basically the same information? Should I change the STAFF table relationship to ASSIGN_LOG table?

STAFF
STAFF_SID [PK]
STAFF_USERNAME

ASSIGN_LOG
A_LOG_SID [PK]
STATUS_SID [FK]
A_SID [FK]
A_LOG_DATE
A_LOG_NOTES
RESOLVE_DATE

Thanks,

Steve C.
kstv@netzero.net

Colin Angus Mackay25-Jun-07 11:18

25-Jun-07 11:18

astv wrote:
Is this ideal to use two different tables storing basically the same information? Should I change the STAFF table relationship to ASSIGN_LOG table?

I was guessing that CreatedBy and AssignedTo would be staff ids (or it could be user ids)

Actually, what is the difference between staff and user in this context?

So in Assignment what does USERS_SID represent? What does STAFF_SID represent? And CreatedBy and AssignedTo? (becuase I'm guessing about all of this)

You aren't, as far as I can see, using two tables to store basically the same information. The Log table shows the historical information, the Assignment table shows the current state along with other information. If you are writing queries that are not interested in historical data then you don't want an additional join to the log table because it will slow things down. It would also be quite a complex join because you'd need to join on the most recent only.

Whether you duplicate the current state in the historical log or not is up to you. I would because it means when you are looking for everything upto and including the current it is there.

So, you might want to set up a trigger on Assignment to INSERT a row in the log to ensure that it is always up-to-date. The minor duplication of data isn't a problem if it is properly managed.

astv25-Jun-07 11:34

25-Jun-07 11:34

In ASSIGNMENT table, USER is the CreatedBy and STAFF is the AssignedTo information. The SID is to prevent freeform entry. a combo box or boxes will be populated with with usernames and the SID is what is passed to the ASSIGNMENT table. I think I am duplicating entries again. I do not think I need the AssignedTo and CreatedBy fields if I am going to keep the USER and STAFF tables.

Do you think I should move the STAFF relationship to ASSIGN_LOG?

I like the trigger idea to INSERT a row in the log to ensure it is up-to-date.

Thanks,

Steve C.
kstv@netzero.net

Colin Angus Mackay26-Jun-07 0:04

Using the LIKE keyword in a query

26-Jun-07 0:04

astv wrote:
Do you think I should move the STAFF relationship to ASSIGN_LOG?

Copy maybe, but not move. It would be good to log that information, but you'd need the current status available also.

reegan4125-Jun-07 5:19

reegan41

25-Jun-07 5:19

Hey,

I'm using this database through MS Access with my program and I need to be able to use the LIKE keyword. My forms have specific, formatted field names that are sequential and uniform. Essentially, the first part of every form field name is a part of its subsequential heading.

IE.

I have a 'heading' column in my table, 'paragraphs'. One example of my headings is 'Scope of Services & Workplan/Methodology Overview'.

Now, if I were to use a query searching for that exact name everything works fine. However, I need to use the LIKE clause as follows:

SELECT heading FROM paragraphs WHERE heading LIKE 'Scope%'

This is because the first part of my form fields are the first words of every heading. ie. Scope_1_chbx, Scope_2_chbx, and so on.

My LIKE clause is resulting in the query returning no results....can anyone help me? Thanks.

---
reegan41

Re: Using the LIKE keyword in a query

Kschuler25-Jun-07 8:47

Kschuler

25-Jun-07 8:47

Your select statement looks okay to me. Perhaps your data is the problem. I've had problems with the LIKE feature because of selecting LIKE 'ABC%' when my data was in lower case, LIKE 'abc%'.

If that is not the case, could you please explain what you are doing further? What do you mean with the Scope_1_chbx, Scope_2_chbx stuff?
Could you give us some actual data from the tables?

Hope this helps.

Re: Using the LIKE keyword in a query

reegan4125-Jun-07 8:51

reegan41

25-Jun-07 8:51

I actually figured it out...believe it or not, it was because I was using LIKE 'data%' instead of LIKE "data*"

For some reason THAT worked instead...odd, but thanks for your help regardless

Re: Using the LIKE keyword in a query

Kschuler25-Jun-07 9:01

Kschuler

25-Jun-07 9:01

You were using an MS Access database? What version?

SQL XML 3 BulkLoad Error

Panchal Hardik25-Jun-07 2:28

Panchal Hardik

25-Jun-07 2:28

I have following error in window service same code is working in window Application

"QueryInterface for interface SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed."
Dim objbl As SQLXMLBulkLoad3
objbl = New SQLXMLBulkLoad3
objbl.ConnectionString = "Provider=SQLOLEDB.1;Data Source=pcDB;Initial Catalog=webdb;User ID=sa;pwd=sa"
objbl.ErrorLogFile = "c:\error.log"
objbl.BulkLoad = True
objbl.KeepIdentity = False
objbl.CheckConstraints = False
objbl.Transaction = False
objbl.Execute("c:\Items.XSD", "C:\Items.xml")

Thanks

Hardik Panchal

Re: SQL XML 3 BulkLoad Error

Dave Kreskowiak25-Jun-07 4:05

Dave Kreskowiak

25-Jun-07 4:05

Don't cross post in multiple forums. It's considered very rude to "spam" the forums and makes it quite difficult for multiple people to collaborate on an answer.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

SQL CE Query Performance

Ferudun Atakan24-Jun-07 22:44

Ferudun Atakan

24-Jun-07 22:44

Hi,

I want to improve the following query's performance executing on sqlce.

select *
from TABLE_A
where field_a in (select field_a
from TABLE_B
where field_b = '0000162080'
and field_c = '2'
and field_d = '011')
order by field_e

Table_A has index for field_a and Table_B has index for field_b and field_c. How can I rewrite the query to execute faster?

thanks,

- ferudun -

Re: SQL CE Query Performance

andyharman24-Jun-07 23:12

andyharman

24-Jun-07 23:12

I don't use SQL CE, but the principles are generally the same for different database products:

Do you have separate indexes on field_b and field_c? If you do then try using a composite index instead:
create index table_b_idx2 on table_b (field_b, field_c, field_d, field_a)

Also, you may be able to get the query to run faster by changing the "select *" bit so that it just returns the columns that you actually need to use.

Select unique column???

Doan Quynh24-Jun-07 22:26

Doan Quynh

24-Jun-07 22:26

I have a table :
ID ___ IDUser ___ Year
1 ___ 1 ___ 2005
2___ 1 ___ 2007
3 ___ 1___ 2003
4___ 2___ 2008
5 ___ 2___ 2005
I want to filt : unique IDUser with Max year :
ID ___ IDUser ___ Year
2___ 1 ___ 2007
4 ___ 2 ___ 2008
Please help me. Thanks.

Colin Angus Mackay24-Jun-07 22:31

24-Jun-07 22:31

Something like this:

SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
            FROM MyTable
            GROUP BY Year) AS s 
ON o.IDUser = s.IDUser AND o.Year = s.Year

Doan Quynh24-Jun-07 22:49

Doan Quynh

24-Jun-07 22:49

Error s: SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year
Exception : "Column IDUser is invalid in the select list because it is not contained in erther an aggregate function or the Group by clause"???

Doan Quynh24-Jun-07 22:54

Doan Quynh

24-Jun-07 22:54

SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY IDUser) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
It is OK. Thanks you very much !

Colin Angus Mackay24-Jun-07 23:50