Database

28-Jan-09 1:18

I have several multi-selection options and its becoming a pain to handle them in a n-n fashion, does SQL2K5 offer an alternative that I might use?

I'll give you an example. I have a patient and a patient can have several symptoms.

This would be the exact opposite of sending an array to the database, it'll be bringing back an array.

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius28-Jan-09 1:28

28-Jan-09 1:28

So you have tables like the following?
Patient
- PatientId (PK)
- Name

Symptom
- SymptomId (PK)
- Description

PatientSymptom
- PatientId (FK)
- SymptomId (FK)

If so, what's the biggest pain in this?

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa28-Jan-09 1:33

28-Jan-09 1:33

That is exactly how it is only you would have a "versioning" of it for the patient. So that for each admittance (the one you helped me out with this morning Smile | :)

) the patient can and most likely will have different symptoms. I just find it painful that it has to go like this, it sorta like sawing against the grain. Implementation wise its not difficult and one can easily knock up a view to end up with a comma separated list of symptoms.

I don't know, I guess I'm just complaining.

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius28-Jan-09 1:42

28-Jan-09 1:42

Could you post any example what operation is painful or is it querying or something else? That would help to understand the problems and to give suggestions.

Based on our previous conversation I understood that you don't use surrogate keys (identity for example). If that's correct, perhaps the problems are related to that.

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa28-Jan-09 2:27

28-Jan-09 2:27

I'm just whining because I had little sleep last night. My baby son is teething.

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius28-Jan-09 2:53

28-Jan-09 2:53

Mustafa Ismail Mustafa wrote:
My baby son is teething

I remember those nights. Luckily it was always one or two nights at a time with both of my kids. If it helps you in anyway, it goes over soon. And then again if you start counting the good sides and the bad sides of having children, there's no question which count is higher Smile | :)

I wish you strength and hopefully you get a good night sleep after this day. Perhaps we continue searching another data modeling solution for you another day Smile | :)

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa28-Jan-09 2:55

28-Jan-09 2:55

Yeah, he's a bundle of joy alright when the poor kid is not in pain.

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Luc Pattyn28-Jan-09 5:06

Luc Pattyn

28-Jan-09 5:06

Mika Wendelius wrote:
what's the biggest pain in this?

(patient=OP, symptom=Headache) Big Grin | :-D

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Wendelius28-Jan-09 5:35

To get the current value for SORT_IN_TEMPDB

28-Jan-09 5:35

It took me awhile to get this since I had completely forgotten the context... After reading the original post I got it. Think I'm Sleepy | :zzz:

The need to optimize rises from a bad design.My articles[^]

Arun Abraham Jose27-Jan-09 18:59

Arun Abraham Jose

27-Jan-09 18:59

I would like to rebuild the indexes of my database by using the query 'ALTER INDEX ALL'. With this I would like to set the option SORT_IN_TEMPDB to ON. But before that I want to know the value set for this particular flag in the table indexes. How can I get the current value of this flag?

I tried using the tables like sys.indexes, sys.stats, sys.schemas etc but miserably failed.

Please advice

With Regards,

Abraham Chethuvelil

Re: To get the current value for SORT_IN_TEMPDB

Wendelius27-Jan-09 19:42

Re: To get the current value for SORT_IN_TEMPDB

27-Jan-09 19:42

AFAIK this information isn't stored when index is created or recreated.

The need to optimize rises from a bad design.My articles[^]

Arun Abraham Jose30-Jan-09 0:33

Arun Abraham Jose

30-Jan-09 0:33

Hi,

Then how do we know what is the value for this flag?

I want to set it on in our database now but I am not sure whether the people who created or maintained this database before were already set it or not?

Another doubt regarding setting this flag is that, will it cause any issues in the future w.r.t. Size or performance and w.r.t. Tempdb as well?

With Regards,

Abraham Chethuvelil

Re: To get the current value for SORT_IN_TEMPDB

Wendelius30-Jan-09 7:27

30-Jan-09 7:27

Arun Abraham Jose wrote:
Then how do we know what is the value for this flag

I don't think it's possible to say afterwards if an index is created with SORT_IN_TEMPDB On or Off. Since this option affects only when the index is created, it isn't necessary information for SQL Server when index is used.

Arun Abraham Jose wrote:
I want to set it on in our database now but I am not sure whether the people who created or maintained this database before were already set it or not

Since this isn't found in the system tables the only thing that comes in mind is that you create a standard that you add an extended property to your indexes. In this property you can document what option was used when the index was created.

Property can be set for example like this:

EXEC sys.sp_addextendedproperty 
       @name=N'SORT_IN_TEMPDB', 
       @value=N'True' , 
       @level0type=N'SCHEMA',
       @level0name=N'DatabaseName', 
       @level1type=N'TABLE',
       @level1name=N'TableName', 
       @level2type=N'INDEX',
       @level2name='IndexName'

After adding this property you can see it's value in system tables and in Management Studio.

Arun Abraham Jose wrote:
Another doubt regarding setting this flag is that, will it cause any issues in the future w.r.t. Size or performance and w.r.t. Tempdb as well?

SORT_IN_TEMPDB affects only index creation. It does not affect how the index behaves after creation so it's creation time option only.

The need to optimize rises from a bad design.My articles[^]

Autoincrement per field

Mustafa Ismail Mustafa27-Jan-09 18:56

27-Jan-09 18:56

OK, I'm not a DB expert but I wanted to do the following and I'm not sure how to go about it. I googled but I think I'm using the wrong name for it.

I have a table that has a foreign key (PatientID) and there is an ID that is Patient relative called AdmittanceID. Now for the sake of clarity I'll give an example of what I want done.

Imagine two patiens, 10 & 20

{Table}
PatientID AdmittanceID
10 01
20 01
10 02

now, the AdmittanceID would autoincrement for each distinct PatientID. I'm stumped as to how to do this in SPs.

Suggestions?

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius27-Jan-09 19:35

27-Jan-09 19:35

You could do this with triggers. Depending on the business rules you would have a bit different implementations. For example if new record gets the maximum value for patient + 1, you would have something like:

CREATE TRIGGER SomeTriggerName
ON YourTableName
AFTER INSERT
BEGIN
   DECLARE @keyValue int;
   DECLARE @patientID int;
   DECLARE newRows CURSOR
   FOR SELECT PrimaryKeyColumnName, PatientId
   FROM inserted;
   --
   OPEN newRows;
   FETCH NEXT FROM newRows INTO @keyValue, @patientId;
   WHILE @@FETCH_STATUS = 0
   BEGIN
      UPDATE YourTableName
      SET    AdmittanceID = (SELECT MAX(AdmittanceID)
                             FROM   YourTableName
                             WHERE  PatientID = @patientId)
      WHERE  YourTableName.PrimaryKeyColumn = @keyValue;
      --
      FETCH NEXT FROM newRows INTO @keyValue, @patientId;
   END
   CLOSE newRows;
   DEALLOCATE newRows;
END

And if you need that the patientid does not have gaps, you would create a delete trigger where you reorder numbering for records having the same patientid as deleted row(s).

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa27-Jan-09 20:51

27-Jan-09 20:51

Brilliant!

Thanks Mika, that is exactly what I needed!

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius27-Jan-09 20:53

Re: Autoincrement per field [edit] [modified]

27-Jan-09 20:53

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa27-Jan-09 21:15

27-Jan-09 21:15

Quick question.

PatientID is a key, but the primary key would be the combined PatientID and the AdmittanceID, this shouldn't affect the implementation correct? Also, why do I need the cursor? Shouldn't the quick select statement in addition to incrementing the value by 1 be sufficient?

There shouldn't be gaps because the values increment for each patient, and each patient's visit is mutually exclusive of the other unless my implementation is way wrong.

[edit]
The way I see it, it can't be done as such, it needs the SP that's doing the insert to find the last admittanceID, increment that and insert it with the rest of the data.

Last modified: 17mins after originally posted --

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius27-Jan-09 21:49

27-Jan-09 21:49

Mustafa Ismail Mustafa wrote:
the primary key would be the combined PatientID and the AdmittanceID, this shouldn't affect the implementation

Actually it does. If it's defined as primary key, you cannot leave it null in the insert statement. Also you cannot insert duplicates. One way could be that you use negative patientid in the insert statement and this trigger modifies the values to correct ones. In that case the where clause would be something like:

...
WHERE  (AdmittanceId, PatientId) = (@OldAdmittanceId, @PatientId);

Also it would be benefitial to have a surrogate key if none is present at the moment. That would make this operations easier. You could change the current primary key to unique key and define the surrogate key as primary key.

Mustafa Ismail Mustafa wrote:
Also, why do I need the cursor

I didn't quickly have any other idea. The problem is that the trigger fires only once when rows are added regardless how many rows are added by the same statement. In other words the trigger isn't executed separately for each inserted row. Now if you add simultaneously two rows for the same patientid and you simply execute an update which sets the patientid to current max in db + 1 those two rows would have the same value for the patientid.

Mustafa Ismail Mustafa wrote:
There shouldn't be gaps because the values increment for each patient

Ok, but what I meant is that is it ok to delete a record later? If it is, I think you would have a gap in patientid numbering. Don't know if it's any problem though.

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa28-Jan-09 1:16

28-Jan-09 1:16

I ended up doing this:

<br />
	@PatientID int, <br />
	@AdmittanceID int, <br />
	@DateAdmitted datetime = GETDATE,<br />
	@NurseNotes int,<br />
	@DoctorNotes int,<br />
	@MiscNotes int<br />
AS<br />
BEGIN<br />
	SET NOCOUNT ON;<br />
<br />
	SET @AdmittanceID = (SELECT MAX(EMR.PatientHistory.AdmittanceID) <br />
						FROM EMR.PatientHistory <br />
						WHERE EMR.PatientHistory.PatientID = @PatientID);<br />
	<br />
	SET @AdmittanceID = @AdmittanceID + 1;<br />
	<br />
	INSERT INTO EMR.PatientHistory<br />
	(PatientID, AdmittanceID, DateAdmitted, NurseNotes, DoctorNotes, MiscNotes)<br />
	VALUES<br />
	(@PatientID, @AdmittanceID, @DateAdmitted, @NurseNotes, @DoctorNotes, @MiscNotes)<br />
END<br />

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius28-Jan-09 1:24

28-Jan-09 1:24

This one is also fine.

If you want, you can combine those statements to a single statement like:

INSERT INTO EMR.PatientHistory
   (PatientID, AdmittanceID, DateAdmitted, NurseNotes, DoctorNotes, MiscNotes)
SELECT @PatientID, 
       MAX(EMR.PatientHistory.AdmittanceID) + 1
       @DateAdmitted, 
       @NurseNotes, 
       @DoctorNotes, 
       @MiscNotes
FROM EMR.PatientHistory 
WHERE EMR.PatientHistory.PatientID = @PatientID

The need to optimize rises from a bad design.My articles[^]

Mustafa Ismail Mustafa28-Jan-09 1:28

28-Jan-09 1:28

wicked cool, thanks for everything Mika Smile | :)

Don't forget to vote if the response was helpful

Sig history

"dad" Ishmail-Samuel Mustafa

Unix is a Four Letter Word, and Vi is a Two Letter Abbreviation

Wendelius28-Jan-09 1:43