Joe,
As you know everything we do on/with the RDBMS affects is performance.
(For the better or worst.) The basic design here should be fine.
My only inquiry is to make sure that the searches your doing are
as efficient as possible. For example you indicated that you only want
to show "Open ticktes" in the table/view. Good. However, I want to
make a simple suggestion. Add a field that positively identifies a
ticket as "Open" and a bit of data driven workflow to maintain that
field in the tickets. I would suggest that a simply table that lists
"Form", 'Status' value and the corresponding 'Open?' field value with
a set field action that is run anytime 'Status' changes would be a
good starting point for the data driven logic. (unless there are
other rules that the business uses to decide what "Open" means.) This
way you can query for records that are 'Open?' = "Yes" instead of
('Status' = 0 OR 'Status' = 1 OR....) or ('Status' <= 5 OR 'Status'
=6). This design also allows you to later add (or ignore) 'Status'
values, and change their mind about "pending" or "Fixed" later and
decide if they are "Open" or not without changing your DB view objects
too. (Because the view would be based on the 'Open?' field instead of
the 'Status' field values.) Sure they would need to adjust existing
values for records on the system too. However if you wanted too you
could even build direct SQL that would do that for them too from the
'Status' --> 'Open?' value mapping form.
Just a few thoughts. HTH.
--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)
Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.
On 7/13/06, Joe DeSouza <[EMAIL PROTECTED]> wrote:
**
This has been done before so I'm inviting comments from those who have done it
and are having a heavily used application that might have a significantly large
number of records in their main data table..
This is what I am in the process of designing.. I am building a completely
custom trouble ticketing application for a cable company. Due to the nature of
their business where they are going to use this trouble ticketing application
to ticket network outages reported both manually as well as automatically from
network monitoring system this table is expected to grow rapidly.
On the control panel on their home page I need to have a table field with a
'thin' list of their open tickets (much the same way as the
SHR:ConsolidatedList in the ITSM application). The design is that instead of
having a place holder Remedy data table to push new tickets on creation with
the functionality to modify and delete enteries in that table, I have elected
to create a database table view of the main data table with the constraint that
it would display only tickets that are in an open status eliminating the
overhead that might be caused by creating an additional table, and the push
fields that happens on every transaction on the main data table.
Then on the control panel I simply build a table field that points to that
database view of the table.
My question is - would this impact performance in anyway.. I think this might
have a positive impact on the performance.. If any of you think otherwise
please do write back with reasons why you think it might not be the best design
to adopt. If any of you think the traditional approach of a thinner Remedy form
to hold the list information is better please do write about that too and your
reasons supporting that approach.
I hope to hear from you guys..
So far my protype right now on my development box rocks, but since I have never
used this approach in a thick data table that has the potential of eventually
housing thousands - maybe a few hundred thousand tickets eventually I would
love comments from those using this approach and their experiences with it...
Note that I will index the necessary columns that are used in the construction
of these database views of the data tables in question so as to optimize the
view creation.
Cheers
Joe D'Souza,
Remedy Consultant / Developer,
Shyle Networks,
New Jersey.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
