refactoring a database and application due to new requirements

My application manages customer's complaints and has already been deployed into production. Each complaint has a code to identify it (for eaxmple "late delivery" ), a "department" type (wich is essentially the department responsible for that kind of complaint) and another "model" code which identifies the route through department's employees this complaint dossier has to follow (first to hr responsible then to hr big boss finally back to customer care). Each dossier has some common info and can have department specific infos, that's why i need deparment code. For example Customer care get a complaint about "rudeness" of a call center operator, opens a dossier with code ABC and type "HR" (there's could be more HR dossier types). When the customer care has filled all the infos, forward it to hr(a mail is sent to the user configured in the system as HR responsible ). The hr employee fills his own section and send it back to customer care.

Till now each complaint code might have only one department and one model, now requirements have changed and i've two problems:

1. Some complaints are identified by the same code but might be due to different departments . For example a complaint about employees rudeness could be sent to the department which rules the call centers or to the department which rules logistics

i could solve this simply extending the table primary key to include the department (hoping they'll not decide the same code for the same department can follow different routes), changing application code might be a bit painful but it can be done :

Does extending primary keys to composite keys is a proble开发者_JAVA技巧m in Oracle or have side effects on existing records? the actual primary key is not used as foreign key anywere and all fields are filled.

1. this is a quite more difficult problem (at least for me): marketing department (the rulers) wants a special dossier.They monitor time departments take to answer complaints and open a new type of dossier if they exceeds the standard time. For the above example, if hr always needs the 30% more time to complete employees rudeness dossiers, marketing can open an "inquire" dossier about that complaint code directed to hr.

   Now, referring to point 1, i could add a new record for each complaint code having the second part of the key being the marketing code and associating it to a new model.This is going to double the rows of the table (which is already quite large). I see it very error prone for inserting new complaint codes.

I know it's very hard to give an opinion without being able to see the schema and the code, but i would appreciate your opinion anyway

"Does extending primary keys to composite keys is a problem in Oracle or have side effects on existing records? the actual primary key is not used as foreign key anywere and all fields are filled."

Oracle allows us to have composite primary keys. They are not a problem from a relational perspective.

The only objection to primary composite keys is the usual one, that they make foreign key relationships and joins more cumbersome. You say you currently don't have foreign keys which reference this table. Nevertheless I would suggest you define a synthetic (surrogate) primary key using an index, and enforce the composite key as a unique constraint. Because you may well have foreign keys in the future: your very predicament shows that your current data model is not correct, or at least not complete.

"i could add a new record for each complaint code having the second part of the key being the marketing code"

Smart keys are dumb. Add a separate column for a marketing code if necessary. This would be populated if Marketing open their own dossier. I don't see why it needs to be associated with the Complaint Code or form part of any primary key (other than the Marketing Code lookup table).

I admit I don't fully understand your data model or business logic, so the following might be wrong. However what I think you want is a table DOSSIERS which can have two dossier types:

• normal dossier identified by DEPT_CODE and COMPLAINT_CODE
• Marketing dossier which I presume would be identified by DEPT_CODE, COMPLAINT_CODE and MARKETING_CODE.

Unique constraints permit NULL columns, so MARKETING_CODE can be optional. This is another advantage of using one instead of a composite primary key.

"I see it very error prone for inserting new complaint codes."

Do you mean creating new complaints? Or new complaint types? Creating new complaints shouldn't be a problem: the process for creating Normal Dossiers will offer a choice of COMPLAINT_CODES where MARKETING_CODE is null, whereas the process for creating Marketing Dossiers will offer a choice of COMPLAINT_CODES where MARKETING_CODE is not null.

If you're talking about adding new complaint types then I suppose the question becomes: does there have to be a separate MARKETING_CODE for each regular COMPLAINT_CODE? I suspect not. In which case, instead of a MARKETING_CODE perhaps you need a CODE_TYPE - values NORMAL or MARKETING.