>>I see, I was asking about tables' schemas: fields' names, types, nullability, default values, check constraints, indexes, PK and FK constraints.
>>
>
>In the Tickets table none of the fields allow NULLs currently, cEntered_Date has GetUserID() as default and tEntered_Date has datetime() as default. The table has updateuserandtime() as a table rule. I didn't put any referential integrity, though I probably should.
>
>The minimum required fields are Caller ID (the issue date and time are populated automatically) and either iProblem_Code or mProblem + either of iSoftware/iHardware/iOther.
>
>>I have more questions.
>>
>>About Tickets:
>>What fields are required to be filled by a client application when a case is created?
>>Are IRESOLUTION_CODE and MRESOLUTION both come from the same record in Resolutions table? If yes, what is the reason for duplicating MRESOLUTION in Tickets?
>
>No, iResolution_Code comes from the table with the predefined resolutions for the most common problems and mResolution allows free form comments (or unique resolution).
>
>>
>>About Resolutions:
>>What fields are required to be filled by a client application when a resolution is created?
>>Is the purpose of Resolutions table to store the list of "unique" resolutions or is new record added for every resolved case?
>>
>>Thanks,
>>Aleksey.
>
>For Resolutions - mResolution is required and either iSoftware_Code or iHardware_Code or iOther_Code or combinations of them (though I need to check, if I really put such validation in place). The purpose of this table is to store common resolutions for the common problems.



Hi Naomi,

Since Resolutions table stores common resolutions, there must be a way for a user to quickly find a particular resolution using some criteria while browsing the list. The iResolution_CODE can't be used for this purpose because I wouldn't expect a user to remember all the codes. I guess MResolution can be used for this purpose and, therefore must be unique. Of course, you can't build an index on a Memo field and comparing Memo fields can be quite slow. I would add one more field to this table, something that will be a unique short description for a resolution. This will be a candidate key, which can be used to get iResolution_CODE from the newly added record.

I am still not sure about Tickets table because don't know all the details about problem domain for the application, but combination of CCALLER_ID, DISSUE_DATE, CISSUE_TIME looks like a possible candidate key.

Thanks,
Aleksey.