Table design questions

[Guest]

I'm writing a Help Desk application. It tracks Help Desk tickets for several groups
The old system (which I am rewriting from scratch) kept a lot of information in the main table and I want to reduce this to minimize network traffic
The Client and Hardware data are all obtained from reference tables and saved in the main Ticket table. The reason being that these tables are imported and when hardware changes and people leave, their records would be lost
How to handle this? I'd like to get this info out of my main Ticket table
Another thing is that several different support areas have different data requirements and all these fields are kept in the main table. It is a one-for-one relationship with the rest of the data but may be inapplicable from ticket to ticket
Foir instance if a ticket is for a mainframe application, there may be information about job name, job number, abend code etc. For other tickets this will all be blank because it is inapplicable
But I am concerned about update synchronization problems if I keep this data in multiple tables
How should this be handled

 

[John Vinson]

I'm writing a Help Desk application. It tracks Help Desk tickets for several groups.
The old system (which I am rewriting from scratch) kept a lot of information in the main table and I want to reduce this to minimize network traffic.

Lots of other good reasons to do this too!

The Client and Hardware data are all obtained from reference tables and saved in the main Ticket table. The reason being that these tables are imported and when hardware changes and people leave, their records would be lost.

Then keep the Client and Hardware data around (in the existing tables
if they're part of your database, or as copies of the tables if
they're not); it doesn't matter whether the client has left or the PC
has been junked, the information is still valid for the time they were
there.

How to handle this? I'd like to get this info out of my main Ticket table.

Certainly. Just the ClientID and the HardwareID should be in Ticket.

Another thing is that several different support areas have different data requirements and all these fields are kept in the main table. It is a one-for-one relationship with the rest of the data but may be inapplicable from ticket to ticket.
Foir instance if a ticket is for a mainframe application, there may be information about job name, job number, abend code etc. For other tickets this will all be blank because it is inapplicable.
But I am concerned about update synchronization problems if I keep this data in multiple tables.
How should this be handled?

This is a good case for "Subclassing", one of the few instances where
one to one relationships are valid. The Ticket table should contain
only those fields in common to all types of help desk tickets
(ClientID, date and time of call, etc.); there should be a limited
number of other tables with the same Primary Key, linked to the
Tickets table Primary Key to Primary Key. These can be kept in synch
by using a Form for the Tickets table, with Subforms for the specific
subclass tables.

I'd see two good possibilities for how to handle it on the form: one
would be to have a separate Tab Page for each class table subform; but
it may be more efficient to have just one Subform control, and to
replace its SourceObject property with the appropriate form in the
AfterUpdate event of the control (listbox I'd guess) identifying which
class of problem is being handled.