WebCOBRA.com Community Message Boards

kippfeldt
Posts: 18
Since: 02/08/2007

Hello,

I was looking for the documentation for the Relational API database. I have looked within the IT resources section and read through other postings and can't seem to find what I need. I am looking for information on how the tables relate (join) to each other and specific definitions/terms for fields within the database tables.

The WEB API had relationships set up so it was easy it figure out how the tables related to each other, but this API does not.

Any help would be great, so I can start developing.

Thanks,
Kipp

jwolgamott
Posts: 257
Since: 09/26/2005

Kipp --

At the current time we don't have an Entity Relationship Diagram (or a different version) to provide.

The main tables are the same hierarchy however:
Customer > Company > Sponsor > BenefitPlan
and
Customer > Company > QB Groups > QB > QBBenefitAssignments

We can answer how specific tables relate to each other though -- just let us know which you're wondering about!

kippfeldt
Posts: 18
Since: 02/08/2007

Thanks for your reply.

Currently I am trying to find the linking for addresses (Adresses table) and don't see any place within the QB tables to get to this information. I am also trying to find general QB data (gender, DOB, age, etc...) and cannot find this information either.

I downloaded the relational API on 3/4/08, so I am downloading again in case there have been changes/fixes added to it.

Thanks,
Kipp

jwolgamott
Posts: 257
Since: 09/26/2005

Both Contact information and what we call parameters (gender, Date of Birth) are centralized. Client/Employers and QBs both have addresses, so there is only one address table.

Benefit Plans and QBs both have parameters, so there is one ParameterModuleData table.

To get contact information:
select * from qb
inner join SystemInformationLinkTable on SystemInformationLinkTable.ModuleId ent = QB.QBAutoID and SystemInformationLinkTable.ModuleTy peID = 3 and SystemInformationLinkTable.InformationTypeID = 1
inner join Addresses on SystemInformationLinkTable.TableLinkIdent = Addresses.AddressID

To get the parameter information for a QB with the fake SSN of 555-55-5555

select parametertypes.ParameterName, parametermoduledata.Parameter, qb.* from qb
inner join parametermoduledata on parametermoduledata.moduleident = qb.qbautoid and parametermoduledata.moduletypeid = 3
inner join parametertypes on parametertypes.parametertypeid = parametermoduledata.parametertypeid
where qb.ssn = '555-55-5555'

You can see the ModuleTypeID exists in both queries... here's the types:
Company: 1
Benefit Plans: 2
QB: 3
Contacts (personnel): 4
Customer (TPA): 5
Users: 8
Departments: 10
Brokers: 15

kippfeldt
Posts: 18
Since: 02/08/2007

Thanks for the info.

I have another question and it deals with the possible difference between the Relational API and WebAPI.

Within the WebAPI "QB_BenefitPlan" table there are two fields called CurrentPremium and CurrentFee, and within the RelationalAPI "QBGroupInformationBenefitCache" table the same fields are there, but it looks like the values are reversed.

example:
WebAPI CurrentFee = 828.44 CurrentPremium = 16.57
Relational API CurrentFee = 16.57 CurrentPremium = 828.44

I could be just looking at the wrong table to get this information, but not sure. We use these two fields within the WebAPI, so I want to make sure that we continue to use them correctly and that I need to reserve the values, look in another table, or wait for a table fix for the Relational API.

Thanks for any help,

Kipp

jwolgamott
Posts: 257
Since: 09/26/2005

Kipp --

I did some precursory checking, and I can't find where the system would be switching the two values.

Looking at the amounts, its obvious that the premium really is 828.44 and the fee is 16.57.

In my examples, that's how its coming across in both the RelationalAPI and the WebAPI.

The QBGroupInformationBenefitCache in the RelationalAPI will be the system of record -- use that if theres a disparity. To answer your specific question, the RelationalAPI is right, and yes, to show current premium costs the easiest way is the QBGroupInformationBenefitCache.

However, can you send over the query you're using in the WebAPI to show the Fee being 828.44? Just send it to spt@travisoft.com and include a link to this page and it'll get to me.

Thanks!

blambrecht
Posts: 65
Since: 02/05/2008

Jesse,

can you elaborate on what the QBGroups table data represents?
I am trying to get benefit plans by company and I beleive there is some link within the QBGroups table, but would like to know more detail about that tables' function.

Thank you,
Bill Lambrecht

blambrecht
Posts: 65
Since: 02/05/2008

In the SystemInformationLink table, is there a way to get a list of each ID and a description for the values in column [InformationTypeID]?

Thank you,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

QB Groups Table: the most common use of the QB Groups table is a family. Since each participant under a plan (primary or dependent) has equal rights, each QB has a benefit assignment to them.

I'd recommend

Companies -> Sponsors -> Benefit Plans -> QB Benefit Assignments -> QB

If you need to only show the Billing contact, let me know.

I'll get the Information link IDs for you by tomorrow.

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

Here you go for the SystemInformationLink Table:

InformationLinkID - AutoID
ModuleTypeID - (Company, QB, Customer, User [values in my post in this thread on 03/26/2008 08:03 AM])
InformationTypeID - (Address, contact, email [values below])
ModuleIdent - Auto ID of the table (QB for ModuleTypeID QB, etc)
TableLinkIdent - Auto ID of the contact table (Addresses for address)

InformationTypeID
1 - Address
2 - Contacts
3 - Phones
4 - Email Address

Note: Contacts themselves can have contact information... The idea here is that a Company can have company addresses, and personnel (both are information for the company). The personnel can then have an email and address of their own.

So, a QB's address would be linked in this table in the following way:
QB.QBAutoID = 35676
Addresses.AddressID = 232

InformationLinkID - PrimaryKey
ModuleTypeID - 3
InformationTypeID - 1
ModuleIdent - 35676
TableLinkIdent - 232

blambrecht
Posts: 65
Since: 02/05/2008

Jesse,

thanks again for timely responses to my questions. Your help is always greatly appreciated!

-bl

kotradov
Posts: 45
Since: 08/25/2008

Do you have any documentation on Table contents and table relationships now? I have a list of items I am trying to find in the Relational database, but I am not having much luck.

kotradov
Posts: 45
Since: 08/25/2008

I did not get a e-mail response to the post above, and was not sure if anyone else did.

chadada
Posts: 24
Since: 08/07/2007

we don't have a document, but there have been a ton of questions asked here in the forums that you can search through to find answers.

kippfeldt
Posts: 18
Since: 02/08/2007

Any new updates on this? It would be nice to have something that would show how the tables are relate to each other.

sszelei
Posts: 53
Since: 09/02/2009

Kipp

We are reworking the mdb file download at present. Companies should have received an advanced email about the changes coming within the next few weeks. These changes are also posted on our blog site at http://blog.travisoft.com/2009/10/3 0/relational-api. Documentation on specific table relations can be acquired by posting to this forum.


Regs,

Steven

dbrandt
Posts: 12
Since: 09/29/2009

Has the status of documentation of the Tables, Relationships etc. changed? We would love to have a data dictionary describing the Tables, Fields, Relationships etc. We will be developing a number of enrollment files and this would be very helpful.

Best regards,

David Brandt

sszelei
Posts: 53
Since: 09/02/2009

David,

We are providing information on an as needed bases for relationships, tables and fields. If you know the data elements you are going after we can provide you with the information you need. We do provide you with code snippets of queries to help you on your way. You can search the message board as much of what you may need has probably already been answered.

Regs,

Steven

ltarver
Posts: 2
Since: 11/21/2008

Hello,
Can you tell me how to query for the data:

1. On the History tab, the Effective Date associated with the Item "Elgibility Notice Printed".

2. On the Event tab, "Date Administrator Was Notified".

Thank you,

Larry

ltarver
Posts: 2
Since: 11/21/2008

Disregard previous post. This is what I found:

1. On the History tab, the Effective Date associated with the Item "Elgibility Notice Printed".

-Found in QBDateTracking table with description Name in QBDateTypes table

2. On the Event tab, "Date Administrator Was Notified".

-Found in QBEventAssignments table under EventNotificationDate field.