# Specification of ITEM_TABLE **Category:** [Technical (archive)](https://discourse.openehr.org/c/technical-archive/156) **Created:** 2006-05-11 12:06 UTC **Views:** 7 **Replies:** 10 **URL:** https://discourse.openehr.org/t/specification-of-item-table/14543 --- ## Post #1 by @Mattias_Forss1 Hello, I'm trying to add support for ITEM_TABLE in the Java Archetype Editor and I have looked at the only archetype I've found that contains a data structure like this. It is openEHR-EHR-OBSERVATION.tendon_babinski_reflexes.v1.adl and it can be found here [http://oceaninformatics.biz/archetypes/](http://oceaninformatics.biz/archetypes/). When I compared the attributes for the ITEM_TABLE in the data structures specifications (found here [http://svn.openehr.org/specification/TRUNK/publishing/architecture/rm/data_structures_im.pdf](http://svn.openehr.org/specification/TRUNK/publishing/architecture/rm/data_structures_im.pdf)) with the ones that existed in the archetype I saw that the attributes had nothing in common. Maybe someone can explain to me how the ITEM_TABLE look like once and for all :-) The archetype looks like this: ITEM_TABLE[at0003] matches { -- structure //// COMMENT: The attributes below adhere to the ITEM_TABLE that you get when you create a table structure in the Ocean Archetype Editor, but I haven't seen any of these three attributes specified anywhere. I would be glad if anyone could point them out to me. rotated matches {True} number_key_columns matches {|1|} rows cardinality matches {0..1; unordered} matches { CLUSTER[at0050] occurrences matches {0..2} matches { -- row items cardinality matches {7; ordered} matches { //// COMMENT: This first element has a CODED_TEXT which contains coded terms that are represented as columns in the Ocean Archetype Editor. ELEMENT[at0012] occurrences matches {0..1} matches { -- row_head value matches { CODED_TEXT matches { code matches { [local:: at0011, -- Left at0013] -- Right } } } } //// COMMENT: This element and all succeding elements are represented as rows in the Ocean Archetype Editor. ELEMENT[at0004] occurrences matches {0..1} matches { -- Biceps value matches { ORDINAL matches { value matches { ... } } } } --- ## Post #2 by @Sam Mattias This is a mistake in the 1.0 specification and it is has been updated on the website 1.0.1 specs. The attribute of a table is rows - each row is a cluster of elements (it was mistakenly documented as columns). rotated is an archetype only attribute which allows rotated tables to be displayed - this is like a pivot table in Excel. Number of key columns is in the new specifications. Hope this is OK. Cheers, Sam Mattias Forss wrote: --- ## Post #3 by @Mattias_Forss1 2006/5/12, Sam Heard <[sam.heard@oceaninformatics.biz](mailto:sam.heard@oceaninformatics.biz)>: > Mattias > This is a mistake in the 1.0 specification and it is has been updated on the website 1.0.1 specs. The attribute of a table is rows - each row is a cluster of elements (it was mistakenly documented as columns). Hi Sam, I looked at this document [http://svn.openehr.org/specification/BRANCHES/Release-1.1-candidate/publishing/architecture/rm/data_structures_im.pdf ](http://svn.openehr.org/specification/BRANCHES/Release-1.1-candidate/publishing/architecture/rm/data_structures_im.pdf)and it says that ITEM_TABLE.columns attribute has been changed to rows in the amendment record (CR-000207). However, the change hasn't actually been commited in the document, so maybe the change was lost somehow... > rotated is an archetype only attribute which allows rotated tables to be displayed - this is like a pivot table in Excel. Is this attribute documented somewhere? I don't really understand why this attribute is allowed under ITEM_TABLE when it is not specified for the actual data structure. > Number of key columns is in the new specifications. This attribute is also missing in the document I mentioned above. By the way, could you explain this attribute to me? Should the users of an archetype editor be able to change the existence and/or contents of this attribute with some GUI component or should it be done automatically whenever new columns are added that contain actual data (ELEMENTs)? Cheers, Mattias --- ## Post #4 by @Sam Mattias > I looked at this document [http://svn.openehr.org/specification/BRANCHES/Release-1.1-candidate/publishing/architecture/rm/data_structures_im.pdf ](http://svn.openehr.org/specification/BRANCHES/Release-1.1-candidate/publishing/architecture/rm/data_structures_im.pdf)and it says that ITEM_TABLE.columns attribute has been changed to rows in the amendment record (CR-000207). However, the change hasn't actually been commited in the document, so maybe the change was lost somehow... > > > rotated is an archetype only attribute which allows rotated tables to be displayed - this is like a pivot table in Excel. > > Is this attribute documented somewhere? I don't really understand why this attribute is allowed under ITEM_TABLE when it is not specified for the actual data structure. If a table is rotated it means that the values in one column become columns headings, with the columns becoming rows. E.g. Reflexes: laterality knee ankle left ++ +/- right + + When this is rotated it becomes: left right knee ++ + ankle +/- + This is called a pivot table and allows rows of the same data type rather than columns which is the usual. This is more than display although the semantics are not effected. Infact, all tables in the editor are rotated at the moment as this is by far the most common way of presenting data in medicine. > > Number of key columns is in the new specifications. This allows unique keys and means that the values of the combined keys have to be unique - if this is not set then there is no restriction on uniqueness. > This attribute is also missing in the document I mentioned above. By the way, could you explain this attribute to me? Should the users of an archetype editor be able to change the existence and/or contents of this attribute with some GUI component or should it be done automatically whenever new columns are added that contain actual data (ELEMENTs)? Tom will be getting to this I think as I did an edit some time ago..Sam --- ## Post #5 by @Mattias_Forss1 2006/5/16, Sam Heard <[sam.heard@oceaninformatics.biz](mailto:sam.heard@oceaninformatics.biz)>: > Mattias > > > I looked at this document [http://svn.openehr.org/specification/BRANCHES/Release-1.1-candidate/publishing/architecture/rm/data_structures_im.pdf ](http://svn.openehr.org/specification/BRANCHES/Release-1.1-candidate/publishing/architecture/rm/data_structures_im.pdf)and it says that ITEM_TABLE.columns attribute has been changed to rows in the amendment record (CR-000207). However, the change hasn't actually been commited in the document, so maybe the change was lost somehow... > > > > > rotated is an archetype only attribute which allows rotated tables to be displayed - this is like a pivot table in Excel. > > > > Is this attribute documented somewhere? I don't really understand why this attribute is allowed under ITEM_TABLE when it is not specified for the actual data structure. > > If a table is rotated it means that the values in one column become columns headings, with the columns becoming rows. E.g. > > Reflexes: > laterality knee ankle > left ++ +/- > right + + > > When this is rotated it becomes: > left right > knee ++ + > ankle +/- + > > This is called a pivot table and allows rows of the same data type rather than columns which is the usual. This is more than display although the semantics are not effected. Infact, all tables in the editor are rotated at the moment as this is by far the most common way of presenting data in medicine. Thanks for your answer Sam, I have another question about this rotated attribute. Why do we need it when it is only a question of how to display the actual data contents? Shouldn't that be decided by an actual user rather than an attribute? I don't think the ADL contents and underlying structure of an ITEM_TABLE should change even if it were possible to switch between pivot tables and ordinary tables. Now to the important question that I'd been thinking about. The specifications of ITEM_TABLE say that the rows attribute should have a List as children but I don't see the point with this. Why do we need several clusters as rows since a table with rows that each have different column headings is impossible to create? If the rows attribute had List as children it would make much more sense. Also, It wouldn't be a bad idea to have rows and column headings as separate attributes instead of having the column headings of the table in the first element under the rows attribute. By the way, I don't think it's usable to show the table data types as columns since that would probably only confuse the user compared to how tree and list are displayed. So for this part I agree with you. > > > Number of key columns is in the new specifications. > > This allows unique keys and means that the values of the combined keys have to be unique - if this is not set then there is no restriction on uniqueness. Why is this attribute's leaf value an integer? I don't understand this part. Does this mean that if the user creates two rows with the same name, for example "knee" then the uniqueness wouldn't be preserved and then another key column would have to be added... Maybe the table should always have an extra column that contain unique keys (as for the ordinal constraint). Hoping for a quick answer. Cheers, Mattias --- ## Post #6 by @Sam Mattias Quick response.. > > > > > > > If a table is rotated it means that the values in one column become columns headings, with the columns becoming rows. E.g. > > > > Reflexes: > > laterality knee ankle > > left ++ +/- > > right + + > > > > When this is rotated it becomes: > > left right > > knee ++ + > > ankle +/- + > > > > This is called a pivot table and allows rows of the same data type rather than columns which is the usual. This is more than display although the semantics are not effected. Infact, all tables in the editor are rotated at the moment as this is by far the most common way of presenting data in medicine. > > Thanks for your answer Sam, > > I have another question about this rotated attribute. Why do we need it when it is only a question of how to display the actual data contents? Well, we do not think it is only a matter of display - although it could be seen as such. If the attribute is not present then it is not possible to determine whether to display it as a pivot or not. The property of DV_QUANTITY is only in the archetype as well and determines valid units - it is a similar sort of issue. > Shouldn't that be decided by an actual user rather than an attribute? I don't think the ADL contents and underlying structure of an ITEM_TABLE should change even if it were possible to switch between pivot tables and ordinary tables. No, I agree. > Now to the important question that I'd been thinking about. The specifications of ITEM_TABLE say that the rows attribute should have a List as children but I don't see the point with this. Why do we need several clusters as rows since a table with rows that each have different column headings is impossible to create? If the rows attribute had List as children it would make much more sense. Also, It wouldn't be a bad idea to have rows and column headings as separate attributes instead of having the column headings of the table in the first element under the rows attribute. Well, each row is a set of elements (as a cluster) - the table is a special case in that each cluster can only have elements (invariant). Each row is a cluster - which is in effect the definition of each column. So you need a list of clusters to get rows. > By the way, I don't think it's usable to show the table data types as columns since that would probably only confuse the user compared to how tree and list are displayed. So for this part I agree with you. We can take this a lot further with time and more power in the archetype editor. Cheers, Sam --- ## Post #7 by @Mattias_Forss1 2006/5/16, Sam Heard <[sam.heard@oceaninformatics.biz](mailto:sam.heard@oceaninformatics.biz)>: > Mattias > > Quick response.. > > > > > > > > > > > If a table is rotated it means that the values in one column become columns headings, with the columns becoming rows. E.g. > > > > > > Reflexes: > > > laterality knee ankle > > > left ++ +/- > > > right + + > > > > > > When this is rotated it becomes: > > > left right > > > knee ++ + > > > ankle +/- + > > > > > > This is called a pivot table and allows rows of the same data type rather than columns which is the usual. This is more than display although the semantics are not effected. Infact, all tables in the editor are rotated at the moment as this is by far the most common way of presenting data in medicine. > > > > Thanks for your answer Sam, > > > > I have another question about this rotated attribute. Why do we need it when it is only a question of how to display the actual data contents? > > Well, we do not think it is only a matter of display - although it could be seen as such. If the attribute is not present then it is not possible to determine whether to display it as a pivot or not. The property of DV_QUANTITY is only in the archetype as well and determines valid units - it is a similar sort of issue. Hi again Sam, I wouldn't mind to ignore and not save this attribute until the different display modes are supported in the Java archetype editor. A missing rotated attribute should probably not cause errors for the Ocean archetype editor? > > Now to the important question that I'd been thinking about. The specifications of ITEM_TABLE say that the rows attribute should have a List as children but I don't see the point with this. Why do we need several clusters as rows since a table with rows that each have different column headings is impossible to create? If the rows attribute had List as children it would make much more sense. Also, It wouldn't be a bad idea to have rows and column headings as separate attributes instead of having the column headings of the table in the first element under the rows attribute. > > Well, each row is a set of elements (as a cluster) - the table is a special case in that each cluster can only have elements (invariant). Each row is a cluster - which is in effect the definition of each column. So you need a list of clusters to get rows. So then I guess the ADL is constructed according to how the first table looks like in your example above (since pivot should not affect the structure)? In that case I believe that the ITEM_TABLEs that are created by the Ocean editor aren't following the specifications, since no matter how many rows ( e.g. left, right) you add, they will only be contained within one cluster only. There is accordingly no way to create an ITEM_TABLE that has more than one cluster under the rows attribute. Furthermore, each row that is added should create a cluster that has an at-code with its name and in the pivot display mode the clusters names will be the column headings. Right? Hope we can sort this out. Regards, Mattias PS. You forgot my question about key columns. See below. --- ## Post #8 by @thomas.beale Mattias Forss wrote: > > 2006/5/16, Sam Heard <sam\.heard@oceaninformatics\.biz <mailto:sam.heard@oceaninformatics.biz>>: > > >     Well, we do not think it is only a matter of display \- although it >     could be seen as such\. If the attribute is not present then it is >     not possible to determine whether to display it as a pivot or not\. >     The property of DV\_QUANTITY is only in the archetype as well and >     determines valid units \- it is a similar sort of issue\. > yes, but that's only because we have an explicit custom type called C\_QUANTITY that supplies the "property" attribute in the archetype\. Otherwise this would not be possible > > Hi again Sam, > > I wouldn't mind to ignore and not save this attribute until the different display modes are supported in the Java archetype editor\. A missing rotated attribute should probably not cause errors for the Ocean archetype editor? This cannot work as explained above \- either the attribute has to exist in the RM class or else we have to create a custom type like C\_ITEM\_TABLE for it\. That's the general rule\. > >>     Now to the important question that I'd been thinking about\. The >>     specifications of ITEM\_TABLE say that the rows attribute should >>     have a List<CLUSTER> as children but I don't see the point with >>     this\. Why do we need several clusters as rows since a table with >>     rows that each have different column headings is impossible to >>     create? If the rows attribute had List<ITEM> as children it would >>     make much more sense\. > I don't understand that\.\.\.\.the current specification says that each row is logically a CLUSTER \(of ELEMENTs\)\. Of course there are more efficient ways to represent tables, but we have stuck to the tree structure used in CEN EN13606 \- it is interoperable and simple to understand\. So you shouldn't worry that this structure could technically allow different column names on different rows, the software will not allow that\. You get a bit of redundancy in using sub\-optimal structures for interoperability purposes\. > >>     Also, It wouldn't be a bad idea to have rows and column headings >>     as separate attributes instead of having the column headings of >>     the table in the first element under the rows attribute\. > >     Well, each row is a set of elements \(as a cluster\) \- the table is >     a special case in that each cluster can only have elements >     \(invariant\)\. Each row is a cluster \- which is in effect the >     definition of each column\. So you need a list of clusters to get rows\. > > So then I guess the ADL is constructed according to how the first table looks like in your example above \(since pivot should not affect the structure\)? In that case I believe that the ITEM\_TABLEs that are created by the Ocean editor aren't following the specifications, since no matter how many rows \( e\.g\. left, right\) you add, they will only be contained within one cluster only\. There is accordingly no way to create an ITEM\_TABLE that has more than one cluster under the rows attribute\. In the archetype you would only have one row, since it is a model for numerous rows in the data\.\.\.does that answer the question? > Furthermore, each row that is added should create a cluster that has an at\-code with its name and in the pivot display mode the clusters names will be the column headings\. Right? this is getting mixed up between rows in an archetype \(don't forget an archetype is not data, it is a model of data\) and the actual data that conform to the archetype \- which will of course have >1 rows in general\. \- thomas --- ## Post #9 by @Mattias_Forss1 2006/5/16, Thomas Beale <[Thomas.Beale@oceaninformatics.biz](mailto:Thomas.Beale@oceaninformatics.biz)>: > Mattias Forss wrote: > > > > > > 2006/5/16, Sam Heard <[sam.heard@oceaninformatics.biz](mailto:sam.heard@oceaninformatics.biz) > > >: > > > >> > > Well, we do not think it is only a matter of display - although it > > could be seen as such. If the attribute is not present then it is > > not possible to determine whether to display it as a pivot or not. > > The property of DV_QUANTITY is only in the archetype as well and > > determines valid units - it is a similar sort of issue. > > > yes, but that's only because we have an explicit custom type called > C_QUANTITY that supplies the "property" attribute in the archetype. > Otherwise this would not be possible > > > > Hi again Sam, > > > > > > I wouldn't mind to ignore and not save this attribute until the > > different display modes are supported in the Java archetype editor. A > > missing rotated attribute should probably not cause errors for the > > Ocean archetype editor? > This cannot work as explained above - either the attribute has to exist > in the RM class or else we have to create a custom type like > C_ITEM_TABLE for it. That's the general rule. Hi Tom, I agree with you. Either the rotated attribute is removed from the archetypes or added in the ITEM_TABLE or a custom type C_ITEM_TABLE. > > > >> Now to the important question that I'd been thinking about. The > >> specifications of ITEM_TABLE say that the rows attribute should > >> have a List as children but I don't see the point with > >> this. Why do we need several clusters as rows since a table with > >> rows that each have different column headings is impossible to > >> create? If the rows attribute had List as children it would > >> make much more sense. > > > I don't understand that....the current specification says that each row > is logically a CLUSTER (of ELEMENTs). Of course there are more efficient > ways to represent tables, but we have stuck to the tree structure used > in CEN EN13606 - it is interoperable and simple to understand. Yes, I guess we're stuck for a while then... > So you shouldn't worry that this structure could technically allow different > column names on different rows, the software will not allow that. You > get a bit of redundancy in using sub-optimal structures for > interoperability purposes. I think we keep flipping around things here. I take it from the point of creating the actual RM data. What I think you mean is that if someone is clumsy it would be quite easy to create two rows with the same column name (not heading), e.g. "Left"? This is quite a flaw but as long as the software (kernel) won't allow this creation it's acceptable. > > > > So then I guess the ADL is constructed according to how the first > > table looks like in your example above (since pivot should not affect > > the structure)? In that case I believe that the ITEM_TABLEs that are > > created by the Ocean editor aren't following the specifications, since > > no matter how many rows ( e.g. left, right) you add, they will only be > > contained within one cluster only. There is accordingly no way to > > create an ITEM_TABLE that has more than one cluster under the rows > > attribute. > In the archetype you would only have one row, since it is a model for > numerous rows in the data...does that answer the question? Yes it does, but not how the column names/headings (depends what you mean if it's displayed as a pivot table or not) are modelled in archetypes, e.g. "left", "right" for a reflexes archetype. They are modelled as the first ELEMENT in Ocean's archetypes and it contains a CODED_TEXT that constrain the headings. Is this the best way to represent headings? I don't understand the logic behind it since they can be mistaken for actual table data which comes after the first element. However, column names/headings are table data too but it's bad design to assume this comes first in order to show the table in an archetype editor. Maybe I could take a different approach than actually showing a real table in the GUI... > > Furthermore, each row that is added should create a cluster that has > > an at-code with its name and in the pivot display mode the clusters > > names will be the column headings. Right? > this is getting mixed up between rows in an archetype (don't forget an > archetype is not data, it is a model of data) and the actual data that > conform to the archetype - which will of course have >1 rows in general. No I haven't forgotten about that but sometimes it's easy to think a step ahead. :-) Regards, Mattias --- ## Post #10 by @Sam Mattias This is a good discussion with some issues that need to be taken further. The problem I face as a clinician is that tables in medicine are often presented with the same data types across the row - and with the values in the first column as column headings. This is a standard pivot table in Excel or Access and is a common data presentation issue - which SQL can perform. I do not believe it is possible to determine from data alone when it is appropriate to rotate a table for presentation. This is why I have pushed to have it in the editor. I think a lot of clinicians will find it difficult to accept information in unrotated form as it is not how they do it now for many situations. There are many situations where this is not the case as well. I agree that the right thing to do is put this in ADL if we want to - we could just say we leave it to the display - but I think this will mean clinicians find it hard to build the tables they need. We can experiment some more with this as it is an ADL issue rather than a reference model issue. I would suggest that you ignore it if you like for the moment (with the result of frustrating the clinicians) or take the attribute as it is and agree to put it into ADL. Cheers, Sam --- ## Post #11 by @thomas.beale Sam Heard wrote: > Mattias > > This is a good discussion with some issues that need to be taken further\. The problem I face as a clinician is that tables in medicine are often presented with the same data types across the row \- and with the values in the first column as column headings\. This is a standard pivot table in Excel or Access and is a common data presentation issue \- which SQL can perform\. I don't have a problem with the rotate idea \- if clinical people want it and it makes sense, then it is needed\. But \- most likely need add an attribute to the reference model\. Here's the problem if we don't: \- archetypes only constraint data that is created \- whether a table is rotated or not becomes important at display / interpretation time of the \_data\_, not the archetype\. \- so any mention of the rotate attribute in the archetype must lead to some flag being set in the data in order to get the behaviour at runtime The only way out of this is that we take the line that the archetype must always be available at the time the data are used, in whatever circumstance\. Technically it isn't hard to engineer this in nice controlled OO environments, but consider that the data might be processed into various kinds of good\-quality or not\-so\-good quality XML, HTML, PDF, other custom formats, where the corresponding archetypes might not be easily available or needed\. I would guess that many applications should be able to process e\.g\. path results as openEHR structures without needing to look up the archetype\. So my conclusion is that we should always ensure that the data can stand alone and remain meanggul\. Now \- in the case of reflexes, visual acuity and other tabular data, one \(presumably\) could argue that the data are perfectly comprehensible even with no 'is\_rotated" flag set; others might argue the opposite\. I personally don't see a problem with including a flag in the RM ITEM\_TABLE class, but we need to do it now\. > > I do not believe it is possible to determine from data alone when it is appropriate to rotate a table for presentation\. This is why I have pushed to have it in the editor\. I think a lot of clinicians will find it difficult to accept information in unrotated form as it is not how they do it now for many situations\. There are many situations where this is not the case as well\. > > I agree that the right thing to do is put this in ADL if we want to \- we could just say we leave it to the display \- but I think this will mean clinicians find it hard to build the tables they need\. We can experiment some more with this as it is an ADL issue rather than a reference model issue\. I would suggest that you ignore it if you like for the moment \(with the result of frustrating the clinicians\) or take the attribute as it is and agree to put it into ADL\. do you mean the reference model Sam \- it can't be "put into ADL" unless we create a C\_ITEM\_TABLE type, which I don't think we should have to do\.\.\.\. But what about the other problem Mattias pointed out \- we don't have an attribute "key\-columns" in ITEM\_TABLE either\. Previously we specified that if there was a "\(key\)" in the name string of a column, it would be a key column, but this is open to abuse by buggy software\. The alternative is to add another attribute into ITEM\_TABLE that contains a list of key column indexes or names\. But I would like to be sure that this feature is really needed in table archetypes\.\.\.is it? \- thomas --- **Canonical:** https://discourse.openehr.org/t/specification-of-item-table/14543 **Original content:** https://discourse.openehr.org/t/specification-of-item-table/14543