根据新需求重构数据库和应用程序
我的应用程序管理客户的投诉并且已经部署到生产中。每个投诉都有一个代码来识别它（例如“延迟交货”），一个“部门”类型（本质上是负责此类投诉的部门）和另一个“模型”代码，用于识别该投诉通过部门员工的路线档案必须遵循（首先交给人力资源负责人，然后交给人力资源大老板，最后回到客户服务部）。每个档案都有一些通用信息，并且可以有部门特定信息，这就是我需要部门代码的原因。 例如，客户服务收到有关呼叫中心接线员“粗鲁”的投诉，打开一个代码为 ABC 的档案并输入“HR”（可能有更多 HR 档案类型）。当客户服务填写完所有信息后，将其转发给 hr（一封邮件将发送给系统中配置为 HR 负责人的用户）。人力资源员工填写自己的部分并将其发送回客户服务部。
到目前为止，每个投诉代码可能只有一个部门和一个模型，现在要求发生了变化，我遇到了两个问题：
- 有些投诉由相同的代码标识，但可能是由于不同的部门造成的。例如，关于员工粗鲁的投诉可以发送到管理呼叫中心的部门或管理物流的部门，
我可以简单地扩展表主键以包括该部门来解决这个问题（希望他们不会为以下部门决定相同的代码）同一部门可以遵循不同的路线），更改应用程序代码可能有点痛苦，但可以做到：
将主键扩展为复合键是否是 Oracle 中的问题或对现有记录有副作用？实际的主键无论如何都不会用作外键，并且所有字段都已填充。
- 这是一个相当困难的问题（至少对我来说）：营销部门（统治者）想要一份特殊的档案。他们监控各部门回答投诉的时间，如果超过标准时间，则打开新类型的档案。 对于上面的例子，如果人力资源部总是需要多 30% 的时间来完成员工的粗鲁行为档案，营销部门可以针对该投诉代码打开一个“查询”档案，并将其发送给人力资源部。
现在，参考第 1 点，我可以为每个投诉代码添加一条新记录，其中键的第二部分是营销代码，并将其关联到新模型。这将使表的行数增加一倍（这已经是相当大）。我发现插入新的投诉代码很容易出错。
我知道在看不到架构和代码的情况下很难给出意见，但无论如何我都会感激你的意见
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:
- 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 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.
- 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Oracle 允许我们使用复合主键。从关系角度来看，它们不是问题。
对主复合键的唯一反对意见是通常的反对意见，即它们使外键关系和连接变得更加麻烦。你说 目前没有引用此表的外键。不过，我建议您使用索引定义合成（代理）主键，并强制将复合键作为唯一约束，因为将来您很可能拥有外键：你的困境表明你当前的数据模型不正确，或者至少不完整。
智能密钥是愚蠢的。如有必要，请为营销代码添加一个单独的列。如果营销部门打开自己的档案，则会填充该列。我不明白为什么它需要与投诉代码关联或构成任何主键的一部分（除了营销代码查找表），
我承认我不完全理解您的数据模型或业务逻辑，因此以下内容可能是错误的，但是我认为您想要的是一个表 DOSSIERS 。可以有两种达析报告类型：
独特的约束允许 NULL 列，因此 MARKETING_CODE 可以是可选的，这是使用一个而不是一个的另一个优点。复合主键。
您的意思是创建新的投诉吗？还是新的投诉类型？创建新的投诉应该不是问题：创建普通档案的过程将提供 COMPLAINT_CODES 的选择，其中 MARKETING_CODE 为空，而创建新的投诉的过程将提供营销档案将提供 COMPLAINT_CODES 选项，其中 MARKETING_CODE 不为空。
如果您正在谈论添加新的投诉类型，那么我想问题是：是否必须为每个常规 COMPLAINT_CODE 有一个单独的 MARKETING_CODE？在这种情况下，您可能需要 CODE_TYPE - 值 NORMAL 或 MARKETING，而不是 MARKETING_CODE。
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.
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:
Unique constraints permit NULL columns, so MARKETING_CODE can be optional. This is another advantage of using one instead of a composite primary key.
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.