Hi,
What is the best practice for extending an existing table? Is it possible to add "user-defined" fields to existing tables, (and if so, what are the naming conventions & procedures, etc), or do we need a custom "local table" that has the same key to provide a one-to-one linking between the standard Tessitura table and our local table? If this is the case, then is there any automation provided by the Web API that will return our custom fields, or must I also build local procedures, call those separately, and join the tables logically in code after retrieving the data from the standard Tessitura web API, and our local procedure API call?
One example would be to provide 3 custom fields for the "TR_Facility" table. Tessitura wouldn't use this data anywhere, but we will use it on our web site.
Thank you,
Larry
Hi Larry
Our practice has been to create a local table, called for example, LTR_Facility_Extended, with the same key field/s, as you say. (and to run a nightly/ad hoc update job to add new rows from the main table to the extended table ,with default values, if you need them to stay in step..
Then you can use the GetLocalDataEx or UpdateLocalData , or ExecuteLocalProcedure API methods to work with the data from your website.
I certainly wouldn’t want to make any changes to native Tessitura tables. Even adding a trigger is something I would think about very carefully before doing.
Ken McSwain Business solutions Manager
kmcswain@sydneyoperahouse.com
T+61 2 9250 7876 F+61 2 9251 7821 M 0418 659 360
SYDNEY OPERA HOUSE BENNELONG POINT
GPO BOX 4274, SYDNEY NSW 2001, AUSTRALIA
SYDNEYOPERAHOUSE.COM
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Larry Brindise Sent: Thursday, 16 December 2010 08:52 To: Ken McSwain Subject: [Tessitura Technical Forum] Customize - extend data tables - add fields to existing tables?
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Please consider the environment before printing this email.=====This message is intended for the addressee(s) named and may contain confidential information.If you are not the intended recipient, please delete it and notify the sender.Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====
It's not really kosher to add to existing standard tables. You'll probably want to go the local table route as you said. Although if what you really need is custom content for the web, you may be able to use the existing content fields and specific content types to accomplish the same thing, depending on what it is.
Like Ken said - check out GetLocalDataEx and UpdateLocalData API methods - as well as ExecuteLocalProcedure (my personal favorite)Also, GetWebContent may prove useful. For example, you could create a content type named 'image' associate that content to a performance or package and enter a URL to a promotional image as the value (/images/logo.png) Then, use this method to return that value which could be used to dynamically display a graphic associated with that performance. Rinse repeat as needed for whatever you like.
Good luck,
Thanks, I'll look into the suggestions and report back with what our solution turns out to be.
Larry,
This is a little bit of a next level solution, but in a previous life I worked with a CRM package that allowed the DBA to effectively do what you mentioned in your initial question. They created a separate table to match each standard table (i.e. t_customer_ext) which they tied together with triggers so that any row in the parent table created a record in the extended table. The local DBA was then free to add any fields they wanted to the extended table (effectively modifying the base install) without worrying about a future upgrade wiping out their changes.