Home » discuss » Public forums » iMIS Community

Using plain old custom tables in an iMIS database

Posted September 20th, 2010 by jyoung
| | | | |

We need to access data in custom tables--not built-in tables that come with iMIS or user-defined tables (UDT’s), but plain old tables that we write in SQL and place into the iMIS database.  Although the iMIS application itself will leave these tables alone, they are used for custom development that involves integrating with iMIS.  Otherwise, they are plain old sql database tables.

We’ve ruled out using Business Object Designer to create Business Objects to access these.  Is better to access these custom tables with stored procedures or with an ORM tool such as NHibernate or Entity Framework?  Will the fact that these tables are in the iMIS database and the fact that these are used for custom development to integrate with iMIS cause one or the other to not work, or otherwise have any problems?

‹ Cannot get Flowdown to workiMIS Mobile ›
login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

RE: Adding SQL tables to iMIS

On September 20th, 2010 rrichter says:

There are a few ways to handle this. Some off of the top of my head are as follows.

1. Keep usign custom tables and then when you need to interact with iMIS you can get at that data. For example, using the iMIS Business Objects, you can write a where clause to get contacts where the "where clause" looks at the custom tables.

2. You could migrate those tables to the iMIS way and then still have access to the data. For example, if you want to keep track of some general key value pairs consider usnig the General Lookup Tables. If you need a lot more data, you could create a user defined table in iMIS, put the data on one KEY record, and hide the tab from eveyone who doesn't need it. That will let you access the data from the iMIS Business Objects and down the road SOA Web Services.

Those are a couple of ways to hold data without the need to create custom tables outside fo the iMIS infrastructure. The final decision would be influenced by the number of tables, number of fields, and number of records.

I usually recommend that you try to keep in the iMIS infrastructue when possible to help with the upgrade path. Although I have created a number of custom tables outside of iMIS in my lifetime!

Thanks,

Randy

login or register to post comments

Thank you for your reply. 

On September 21st, 2010 jyoung says:

Thank you for your reply.  I agree that staying within the iMIS framework wherever it makes sense to do so is a good idea to take advantage of existing features instead of reinventing the wheel.  What I'm asking about, however, is situations where features of iMIS are not suitable for the need at hand, or are an awkward fit--where they cannot be used to meet requirements without hacks.  For these, custom tables are preferable or even necessary.  What I hope to find out is how best to access them (via stored procedures or an ORM tool).

Does all of that make sense?

login or register to post comments

We use dozens of non-iMIS

On September 28th, 2010 steveg says:

We use dozens of non-iMIS tables in our iMIS 10.6 database. Consider using a prefix so you can easily tell them from the built-in ones. And don't forget about Merge (presumably their primary key is ID); you'll need to have a routine that cleans up/takes appropriate action (we have a trigger on Activity that fires when Activity_type='MERGELOG').

Mostly we use storeprocs to read/write the data. Our reports use the tables directly, mixing with iMIS and the custom table tables.

 

 

login or register to post comments

A few things to think about

On October 11th, 2010 chris_castle says:

We also have a lot of non-iMIS tables in our iMIS database that we use in applications that work with iMIS.  We use a framework to access these tables.  Before you continue I have these questions/comments:

1.  This is a question that must be answered at some point because all data doesn't fit into iMIS easily.  It is best to come up with a strategy now so that you don't end up in a mess later.

2.  We do not use Business Object Designer for all of our tables, but we do use it occasionally to pull in a table or two so that IQA can access them.  This is not the way we access them, but it is nice for iMIS to be able to query them as well.

3.  I agree with using a prefix on your tables to make them easy to find.  The other added benefit is that you will not have to worry about ASI creating a table in a future version that conflicts with one of your tables. 

4.  Do you plan to enforce referential integrity between iMIS and your tables?  If not, I would consider adding a second database to your server because triggers are dropped with every iMIS upgrade.  You will have to recreate these unless you use Taskcenter.  You can always create views in the iMIS db to make reporting easier.

5.  Remember that ASI will do things like drop triggers in an upgrade.  If you use many triggers, I recommend TaskCenter to manage them so this gets fixed for you.  If you do not, you need to be diligent in managing these during upgrades.

All of this said and a few years of experience tells me that you can easily put non-iMIS tables in your database.

 

login or register to post comments