Database

Is it possible to access a row in a datatable in constant time?

25-Jun-07 11:32

Use DATEADD

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

Ryan Andrus25-Jun-07 9:34

Re: Is it possible to access a row in a datatable in constant time? [modified]

25-Jun-07 9:34

Is there a way to configure\create a datatable (strongly typed or untyped)
that can have a row accessed in constant time if you are searching on the
primary key?

If there isn't a way, then what is the quickest way to access a row when
using datatables?

Thanks,

Ryan

"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction

Colin Angus Mackay25-Jun-07 9:46

Re: Is it possible to access a row in a datatable in constant time?

25-Jun-07 9:46

Ryan Andrus wrote:
have a row accessed in constant time

What is "constant time"?

Additional: To the person that voted this a 1. Why is this not a valid question? When I read the OP's request I wondered if he meant "real time" or something else. So I asked for clarification. What is so wrong with that?!

-- modified at 19:49 Tuesday 26th June, 2007

Ryan Andrus25-Jun-07 10:29

Re: Is it possible to access a row in a datatable in constant time?

25-Jun-07 10:29

O(1) in Big O notation which basically means that the amount of time to find one row in the datatable is independent of the number of rows. In other words, it will always take the same amount of time to access a row in the datatable. The same way that getting an element in an array in C++ is constant time because it is done through pointer arithmetic and not by iterating through the elements.

"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction

Arun.Immanuel25-Jun-07 21:05

Arun.Immanuel

25-Jun-07 21:05

Please have a look at Indexes and Clusters.

I think it will reduce the time from O(n) to O(log n).

Regards,
Arun Kumar.A

Re: Is it possible to access a row in a datatable in constant time?

Ryan Andrus26-Jun-07 3:46

Re: Is it possible to access a row in a datatable in constant time?

26-Jun-07 3:46

Are you refering to index's on the Database table? That is not what I am asking about. I am talking about System.Data.DataTable. If there is a way to index a System.Data.DataTable can someone please post up a how to?

"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction

Ryan Andrus26-Jun-07 5:08

How to upload a picture in the database & how to make the search Case sentive

26-Jun-07 5:08

I am asking about System.Data.DataTable not a table in the database. Do System.Data.DataTable's support index's?

"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction

Bahaa Hany25-Jun-07 6:40

Bahaa Hany

25-Jun-07 6:40

Hi
i have 2 questions in SQL 2005
First:
How to upload a picture in the database as i want each user to put his pic
second:
How to make the search CaseSentive
so it can diffrentiate bet
bahaa & BaHaa

Thanks a lot

Re: How to upload a picture in the database & how to make the search Case sentive

Colin Angus Mackay25-Jun-07 6:53

Re: How to upload a picture in the database & how to make the search Case sentive

25-Jun-07 6:53

Bahaa Hany wrote:
How to upload a picture in the database as i want each user to put his pic

Use a varbinary(max) column to store this. When communicating between the database and your application use parameters. The data must be sent as a byte array (byte[] in C#)

Bahaa Hany wrote:
How to make the search CaseSentive

Change the collation sequence to a case sensitive (CS) one. This is one of the options at install and is the best time to make that desision because it is a PITA to change it afterwards. Also, you will have great difficulty making comparisons between columns in different collation sequences so it is best pick one and apply it across the whole server.

Dave Kreskowiak25-Jun-07 7:14

Dave Kreskowiak

25-Jun-07 7:14

Have a little patience. Reposting the same question, "spamming", within 24 hours will likely get you ignored in the future.

A guide to posting questions on CodeProject[^]

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

Need help on table design

astv25-Jun-07 6:20

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.

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