Author Topic: Non-unique indexes  (Read 797 times)

Thomas Mercer-Hursh

  • EA User
  • **
  • Posts: 386
  • Karma: +0/-0
  • Computing Integrity
    • View Profile
Non-unique indexes
« on: November 09, 2007, 03:09:42 pm »
Understanding that there are some *issues* about primary keys, uniqueness, and the like ... but I haven't really had to come to grips with these yet since I am trying to model an existing database rather than modeling a future database.  I presume that if one has an index which is not primary, but which is unique, then it can also be modeled as a constraint.  But, what about non-unique indices, whether single part or multi-part.  Where should one model them?

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
Re: Non-unique indexes
« Reply #1 on: November 09, 2007, 03:31:23 pm »
Hi Thomas,

You can still model them as constraints, much as you do for other non-primary indexes. Just don't check the Unique box for any of the participating fields.

Oh yes, choose «index» as the stereotype for the operation when you set it up.

You might want to read through the recent 'chatter' on this stuff. While most (if not all) of it was directed at unique indexes, some of it might apply to other indexes as well.

AFAIR pretty much everything as involved multi-field indexes. If you are simply indexing some single fields for performance and such you're likely OK.

David
No, you can't have it!

Thomas Mercer-Hursh

  • EA User
  • **
  • Posts: 386
  • Karma: +0/-0
  • Computing Integrity
    • View Profile
Re: Non-unique indexes
« Reply #2 on: November 09, 2007, 03:42:28 pm »
Current best practice would point to single column indexes since the database engine can do multi-index query resolution, but then I am modeling legacy code, so chances are it will be a bunch of columns ... but, you're having said that got me to looking in the help and I see that the stereotypes and procedures are already there, so ... nevermind!  Thanks.

Thomas Mercer-Hursh

  • EA User
  • **
  • Posts: 386
  • Karma: +0/-0
  • Computing Integrity
    • View Profile
Re: Non-unique indexes
« Reply #3 on: November 09, 2007, 04:15:40 pm »
I notice in the help that they refer to the Columns tab ... does that actually mean the Parameters button?

Also, I don't see any Unique checkbox when using the <<index>> stereotype so does this mean I put something in the post condition?
« Last Edit: November 09, 2007, 04:17:05 pm by tamhas »

Thomas Mercer-Hursh

  • EA User
  • **
  • Posts: 386
  • Karma: +0/-0
  • Computing Integrity
    • View Profile
Re: Non-unique indexes
« Reply #4 on: November 09, 2007, 04:29:22 pm »
Also, in OpenEdge databases each index component can be flagged as ascending/descending and Abbreviate (allows for partial field matches without LIKE -- a deprecated feature), but I don't see any way to flag these in the parameters dialog unless it would be via a stereotype.  Is that what you would do?

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 6044
  • Karma: +73/-83
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Non-unique indexes
« Reply #5 on: November 09, 2007, 06:50:38 pm »
Quote
Also, in OpenEdge databases each index component can be flagged as ascending/descending and Abbreviate (allows for partial field matches without LIKE -- a deprecated feature), but I don't see any way to flag these in the parameters dialog unless it would be via a stereotype.  Is that what you would do?
Thomas,

this is one aspect of DB indexes that EA does NOT support (as of {818})

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

RoyC

  • EA Administrator
  • EA Practitioner
  • *****
  • Posts: 1166
  • Karma: +9/-3
  • Read The Help!
    • View Profile
Re: Non-unique indexes
« Reply #6 on: November 11, 2007, 03:35:20 pm »
Quote
I notice in the help that they refer to the Columns tab ... does that actually mean the Parameters button?

Also, I don't see any Unique checkbox when using the <<index>> stereotype so does this mean I put something in the post condition?


Are you using this procedure?

http://www.sparxsystems.com.au/EAUserGuide/index.html?indexestriggers.htm

There is a specific Columns tab on the Operations dialog, and there is no Unique checkbox.

If you are creating a table (as in

http://www.sparxsystems.com.au/EAUserGuide/index.html?o411.htm)

and then you define table columns (as in

http://www.sparxsystems.com.au/EAUserGuide/index.html?createcolumns.htm )

Then you display a Table Columns dialog, which contains the Unique checkbox.

Note that there is no Column element in the Toolbox in EA Release 818 - you access columns only as attributes of the table. Also, the procedure refers to the Table: Attributes dialog, but it is now the Table: Columns dialog.

I can't find the Parameters button in this context - which dialog are you using?

HTH

Roy
Best Regards, Roy

Thomas Mercer-Hursh

  • EA User
  • **
  • Posts: 386
  • Karma: +0/-0
  • Computing Integrity
    • View Profile
Re: Non-unique indexes
« Reply #7 on: November 11, 2007, 03:44:48 pm »
I think my problem here is that I am following that procedure ... now ... but my stereotype is «oeTable» instead of «table» ... this seems to break a number of important things, even though my sterotype is based on table.

jeshaw2

  • EA User
  • **
  • Posts: 701
  • Karma: +0/-0
  • I'm a Singleton, what pattern are you?
    • View Profile
Re: Non-unique indexes
« Reply #8 on: November 29, 2007, 08:05:06 pm »
Pardon my static for I don't use UML often for database modeling.  But, going where others fear to tread, I just started a fresh Data Model diagram and added myTable to it.  Then I defined a column myIndex and an index named myIndex.  Then, to myTable, I added a constraint myIndex=ascending which gave me a diagram I kinda liked.  I don't think the code generator will understand what I did, but my DBA will.  ;D

What am I not understanding?  Are we going for something that the code generater will understand?

Jim
« Last Edit: November 29, 2007, 08:07:28 pm by jeshaw2 »
Verbal Use Cases aren't worth the paper they are written upon.

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 6044
  • Karma: +73/-83
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Non-unique indexes
« Reply #9 on: November 29, 2007, 08:32:57 pm »
Quote
What am I not understanding?  Are we going for something that the code generator will understand?

Jim,

What's currently missing is the ability to create a concatenated index where each column can be individually set as ascending or descending.

eg:
Termination_Index(TerminationDate (desc), SurrogateKey (asc))

At present you can only apply ascending/descending to the entire concatenated key.

HTH,
Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

jeshaw2

  • EA User
  • **
  • Posts: 701
  • Karma: +0/-0
  • I'm a Singleton, what pattern are you?
    • View Profile
Re: Non-unique indexes
« Reply #10 on: November 30, 2007, 09:06:19 am »
OIC!  Thanks Paolo.

Humm...I don't even like the way concatenated indexes appear on a diagram.  You get the data types of the columns, but not their names.   UI to set this up is not very intuitive either. EA's help file is silent on all of this too. ::)

Another opportunity for Sparks to excel! :)
Verbal Use Cases aren't worth the paper they are written upon.

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 6044
  • Karma: +73/-83
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Non-unique indexes
« Reply #11 on: November 30, 2007, 03:08:13 pm »
Quote
OIC!  Thanks Paolo.

Humm...I don't even like the way concatenated indexes appear on a diagram.  You get the data types of the columns, but not their names.   UI to set this up is not very intuitive either. EA's help file is silent on all of this too. ::)

Another opportunity for Sparks to excel! :)
Use the diagram properties to change the display to name only.  I've already suggested to Sparx this should be the default for Data Model diagrams.

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!