Keywords: Dual table vs single table

[Rocky_Barz]

Hi all,

Newbie here.  I tried searching, but couldn't find anything specifically geared toward my question.  We've got a keyword type that consists of a simple yes/no dropdown.  There is a hot debate going on around the office as to wether we should make it single or dual table.

Conversly, the debate now has spilled over to a "last name" keyword that, obviously, can have a large amount of unique values.  That seems like an obvious single table candidate, but now we aren't sure.  The spectre of doubt has crept in, and we can't seem to hash it out.  Documentation on this issue provided by our third-party vendor was of no help whatsoever.

Any guidance?  Can anyone state the differences between the two in a way that will click in our aching brains?  Thanks in advance for all the help!!

[John_Anderson4]

I'd just go single-table for everything. It's easier all around and there shouldn't be any performance impact.

If you have a lot of repeated values (like YES/NO) then using a dual-table may save some database space, but they should both perform fine.

From what I've heard, it used to make more of a difference, but in the later versions of OnBase single-table has been pretty much recommended for everything.

[David_Villapudu]

Here is a quote from the SysAdmin MRG version 11.0 from page 786:

It is considered a best practice to use single table storage for searchable keywords, as well as in instances where one keyword on a document changes constantly (e.g., Workflow Status keywords). Use single table storage for alphanumeric keywords, unless a dynamically populated drop-down list is required. When a dynamically populated drop-down list is required, it is considered a best practice to use dual table storage. Dual table storage should only be used when a dynamically populated drop-down list is required.

[Jonathan_Perreg]

Here's another thing to consider. For YES/NO values I agree that a single table keyword is better. However, when you have a LAST NAME type of keyword, there is one compelling reason to favor a dual table keyword. In the Keyword Type dialog box, Configuration has the ability to make global substitutions for dual table keywords. What this means is that if someone's last name was spelled "OMAMA" instead of "OBAMA" then all you have to do is index at least one document correctly, then use the keyword tool to replace "OMAMA" with "OBAMA" across the board. If thousands of documents and dozens of document types shared this one keyword type and were indexed with "OMAMA", then you would be repairing all of them in one shot.

[#5]

Hello Rocky,

We usually recommend that if you have a drop-down with relatively smaller set, that you use a dual-table. However, if you think that drop-down dataset will be growing expotentially, then might as well use single-table. Also, for last name it will be much efficient to use single table, unless you know that you have only a defined set of last names that you are going to use.

Please let me know if you have more questions.

~Riddhi