Structure of thesaurus database tables in Microsoft Access database

The Microsoft Access database file to be imported into CodeSells Thesaurus Builder should have four tables:

 

Thesaurus Table

Thesaurus table contains all properties of the main language. It is the master table of ‘Translations’ and ‘NPTerms’ tables. Here you can find a short description of each field:

No.

Field Name

Data Type

Field Size

Required

Description

Repeatable

1

LineNum

Number

Long Integer

Yes

The table must be sorted by this field.

No

2

TermLevel

Number

Long Integer

Yes

The level of term in the thesaurus tree.

No

3

Term

Memo

 

Yes

 

No

4

RelationTypeToBT

Number

Long Integer

 

The code of the hierarchical relationship.

No

5

ConceptNumber

Number

Long Integer

 

 

 

6

RT

Memo

 

 

Related Term

Yes

7

Opposite

Memo

 

 

Opposites

Yes

8

SN

Memo

 

 

Scope Notes

No

9

Synonyms

Memo

 

 

 

Yes

10

Frequency

Number

Long Integer

 

 

No

11

Description

Memo

 

 

 

No

12

InputDate

nText

30

 

 

No

13

ApprovalDate

nText

30

 

 

No

14

LastUpdate

nText

30

 

 

No

15

NotValidDate

nText

30

 

 

No

16

ExternalLink

Image

 

 

 

No

17

Hide

Yes/No

 

 

 

No

18

Status

Number

Long Integer

 

  1. Candidate

  2. Provisional

  3. Approved

  4. Not valid

No

19

SC

Memo

 

 

Subject Category

No

20

ABB

Memo

 

 

Abbreviation

No

21

ABF

Memo

 

 

Abbreviation For

 

22

HN

Memo

 

 

Historical Notes

No

23

CA

Memo

 

 

Chemical Abstracts Registry Number

Yes

24

EC

Memo

 

 

IUBMB Enzyme Committee Number

Yes

25

CT

Memo

 

 

Corresponding Terms

Yes

26

INE

Memo

 

 

Identical National Equivalent

Yes

27

NNE

Memo

 

 

Nearest National Equivalent

Yes

28

TermImage

Image

 

 

The image which is assigned to the term

No

29

AdditionalNotes

Memo

 

 

Additional design time information about the term.

No

 

Translations Table

‘Translations’ table consists of the translations and their properties of the current thesaurus terms. This table is a detailed table of the ‘Thesaurus’ table and the master table of the ‘TranslationsNP’ table. The fields and properties of this table are listed below:

No.

Field Name

Data Type

Field Size

Required

Description

Repeatable

1

LineNum

Number

Decimal

Yes

The table must be sorted by this field.

Divide this field in two parts:

  1. Bits 0 – 31: Language Number. See Supported Languages

  2. Bits 32-63: Equal to the field LineNum of Thesaurus table.

No

2

AccessKey

Number

Decimal

Yes

Equal to the field LineNum of Thesaurus table.

No

3

Term

nText

 

Yes

The translation of the field Term of the Thesaurus table

No

4

ConceptNumber

Number

Long Integer

Yes

Equal to the field ConceptNumber of Thesaurus table.

No

5

SN

Memo

 

 

Scope notes for the current translation language.

No

6

Synonyms

Memo

 

 

Synonyms for the current translation language.

Yes

7

Description

Memo

 

 

Description for the current translation language.

No

8

ABB

Memo

 

 

Abbreviation of the current language translation.

No

9

ABF

Memo

 

 

Abbreviated term of the current language translation.

 

9

HN

Memo

 

 

Historical notes for the current translation language.

No

 

TranslationsNP Table

The non-preferred terms of the translations are saved into the ‘TranslationsNP’ table. This table is a detailed table of the ‘Translations’ table. See the fields and properties of ‘TranslationsNP’ table here:

No.

Field Name

Data Type

Field Size

Required

Description

Repeatable

1

LineNum

Number

Decimal

 

The table must be sorted by this field.

No

2

AccessKey

Number

Decimal

Yes

Equal to the field LineNum of Translations table.

No

3

Term

nText

 

Yes

The non-preferred term of the field Term of the Translations table

No

4

ConceptNumber

Number

Long Integer

Yes

Equal to the field ConceptNumber of Translations table.

No

5

SN

Memo

 

 

Scope notes for the current translation language.

No

6

Description

Memo

 

 

Description for the current translation language.

No

7

HN

Memo

 

 

Historical notes for the current translation language.

No

8

InputDate

nText

30

 

 

No

9

ApprovalDate

nText

30

 

 

No

10

LastUpdate

nText

30

 

 

No

11

NotValidDate

nText

30

 

 

No

12

UForSF

Bit

 

 

Determines the type of the non-preferred term in the thesaurus.

  1. Used For

  2. See for

No

13-56

TranslationFields

Memo

 

 

  • The equivalence terms of the non-preferred terms in other languages.

  • The field names are the name of the languages. See Supported Languages

No

 

NPTerms Table

The non-preferred terms of the main language terms of the thesaurus are saved into the ‘NPTerms’ table. This table is a detailed table of the ‘Thesaurus’ table. Here you can find a short description about its fields and properties:

No.

Field Name

Data Type

Field Size

Required

Description

Repeatable

1

LineNum

Number

Decimal

 

The table must be sorted by this field.

No

2

AccessKey

Number

Decimal

Yes

Equal to the field LineNum of Thesaurus table.

No

3

Term

nText

 

Yes

The non-preferred term of the field Term of the Translations table

No

4

ConceptNumber

Number

Long Integer

Yes

Equal to the field ConceptNumber of Translations table.

No

5

SN

Memo

 

 

Scope notes for the current translation language.

No

6

Description

Memo

 

 

Description for the current translation language.

No

7

HN

Memo

 

 

Historical notes for the current translation language.

No

8

InputDate

nText

30

 

 

No

9

ApprovalDate

nText

30

 

 

No

10

LastUpdate

nText

30

 

 

No

11

NotValidDate

nText

30

 

 

No

12

UForSF

Bit

 

 

Determines the type of the non-preferred term in the thesaurus.

  1. Used For

  2. See for

No

13-146

TranslationFields

Memo

 

 

  • The equivalence terms of the non-preferred terms in other languages.

  • The field names are the name of the languages. See Supported Languages

No

 

UserDefinedRelations Table

All the information of the user defined relationships are collected into this table. Here you can find a short description about its fields and properties:

No.

Field Name

Data Type

Field Size

Required

Description

Repeatable

1

Type

Number

Long Integer

Yes

The type of the relationship: Associative or Hierarchical

No

2

Description

nText

 

Yes

The name of the relation

No

3

Reciprocal Relation

nText

 

Yes

The name of the reciprocal relation

No

4

Code

Number

Long Integer

Yes

The code of relation automatically generated by the application

No

5

ReciprocalCode

Number

Long Integer

Yes

The code of reciprocal relation automatically generated by the application

No

6

SameRelation

Yes/No

 

Yes

Yes, if the relation and its reciprocal relation are the same

No

7

Status

Number

Long Integer

Yes

The Current status of the relation.

bit 0: Disabled

bit 1: Removed

No

 

See also: Export to Microsoft Access