Notes系统在数据库中 [英] Notes system in database

问题描述

我目前正致力于在Access 2013中构建一个ERP / CRM数据库，以涵盖运营业务的所有非财务方面，例如客户/供应商信息，联系人，订单和交付跟踪等。 / p>

我们制造定制的细木工产品（窗户，门等）。我发现的一件事是，我需要有能力在数据库中的几乎每个表中记录记录。例如，我可能要对以下表格写上注释：

• tblAccount 其保存客户和供应商信息，例如


• tblDelivery - 客户要求在伦敦路的房子后面的驾驶员公园


• tblQuote - 报价不包括其他人提供的钢材的木材包壳。

等。等等。

所以目前我有一个 tblNote ，它有以下字段：

• NoteID - PK


• AccountID - FK到 tblAccount


• DeliveryID - FK tblDelivery c> c> tblQuote


• 其他包含我可能需要保留笔记的表格的其他FK。


• 

code> EntryDate - 输入日期备注

• NoteText / li>
  
• CommTaskID - 稍后解释 -

输入新注释时，大多数其他表格的FK将为空（除了注释实际涉及的那些）。笔记将在子表单上查看，例如。当浏览客户帐户时，会有一个框显示关于该客户的注释。

在自己的表中记录笔记的原因是数据库中的每个记录

我还希望实现任务分配和跟踪以及通信日志记录。例如，当我们收到来自一个潜在的新客户的电话呼叫请求报价时，我们将记录接到呼叫，客户的联系方式，注意他们已经请求报价以及他们想要报价的细节，加上标志为特定数据库用户或指定的nobody所需的操作。它也可以创建为

考虑到这一点我有 tblCommTaskLog ，它有以下字段： p>

• CommTaskID - PK


• CommMethodID - FK到 tblCommMethod ，其中包含电话 c $ c>传真，电子邮件和个人。


• AccountID - 进行联系的人（客户/供应商）的帐户记录


• 输入此记录的用户的EnteredByUserID FK。


• EntryDate


• IsActionRequired - Boolean


• ActionRequiredByUser - FK


• ActionCompletedDate - 日期时间操作已标记为已完成。


• ActionCompletedByUser - 完成任务的用户的用户表的另一个FK。 $ b

如上所示，在注释表中有一个 CommTaskID ，这将允许您创建注释记录其中包含有关沟通或任务的任何相关注释。

基本上我的问题是：有更好的办法吗？我缺少什么？这似乎是一个笨重的方式去处理事情，但我不能提出一个更好的一个遵循数据库规范化规则。

解决方案

你的概念听起来对我来说。我不得不将笔记集成到一个现有的数据库中，你绝对只需要一个Notes表与一个字段，指定该笔记属于哪个表，以及该表中该笔记绑定的主键。

所以，我想也许你想要这样：

• NoteID - PK


• TableID - FK到存储您的表名称的新表


• TablePK - FK到表中的主键在TableID中引用


• 包含我可能需要
  以保存笔记的其他表的其他FK。




• EntryDate - 输入日期备注


• NoteText - 注释的文本


• CommTaskID - 稍后解释 -

在查询中，您可以简单地将TablePK连接到您所在的表中的键表格和过滤器基于您加入的表格的TableID。

或者，如果您需要设置关系，您可以简单地添加一个新字段到所有表中包含它们的TableID。

I'm currently working on building an ERP/CRM database in Access 2013 to cover all non-financial aspects of running a business such as customer/supplier info, contacts, order and deliveries tracking, and that sort of thing.

We manufacture made-to-measure joinery products (windows, doors etc). One thing that I'm finding is that I'll need to have the ability to write notes about records in almost every table in the database. For example, I may want to write notes on the following tables:

• tblAccount which holds customer and supplier info, e.g. "This customer always has special glass".
• tblDelivery - "Customer has requested that driver parks at back of house on London Road".
• tblQuote - "Quote does not include timber cladding for steels - supplied by others".

etc. etc.

So currently I have a tblNote which has the following fields:

• NoteID - PK
• AccountID - FK to tblAccount
• DeliveryID - FK to tblDelivery
• QuoteID - FK to tblQuote
• Several more FKs to other tables which contain things I might need to keep notes on.
• EnteredByUsedID - FK to tblUser of the database user who entered the note onto the system.
• EntryDate - Date note was entered
• NoteText - the text of the note
• CommTaskID - Explained later - see below

When entering a new note, most of the FKs to other tables will be blank (except those which the note actually concerns). The notes will be viewed either on a subform, e.g. when browsing through customer accounts there will be a box which shows notes about that customer.

The reason for having notes in their own table is that each record in the database (e.g. a customer account) may need to have more than one note attached to it.

I also want to implement task assignment and tracking, and communication logging. For example when we receive a phone call from a prospective new customer asking for a quotation we would record that the call was received and when, the customer's contact details, note that they have requested a quote along with minor details about what they want quoted, plus flag it for action required either by a specific database user or with nobody specified. It might also be created as a

With this in mind I have tblCommTaskLog which has the following fields:

• CommTaskID - PK
• CommMethodID - FK to tblCommMethod which holds things like Phone call, Fax, Email and In Person.
• AccountID - account record of the person (customer/supplier) making the contact
• EnteredByUserID FK of user who entered this record.
• EntryDate
• IsActionRequired - Boolean
• ActionRequiredByUser - FK to user table to flag who needs to take action on this item (could be blank if this action can be completed by anybody).
• ActionCompletedDate - Date when the action was marked as completed.
• ActionCompletedByUser - Another FK to user table for user who completed task.

As seen above there is then a CommTaskID in the notes table which would allow you to create note records which contain any relevant notes about the communication or task.

Basically my question is: Is there a better way to go about this? Am I missing something? It seems a clunky way to go about things but I'm unable to come up with a better one which follows DB normalisation rules.

解决方案

Your concept sounds right to me. I've had to integrate notes into an existing DB and you definitely only want one Notes table with a field that designates which table the note belongs to, and which Primary Key within that table the note is tied to.

So, I think maybe you want this:

• NoteID - PK
• TableID - FK to a new table, which stores the names of your tables
• TablePK - FK to the Primary Key in the table referenced in TableID
• Several more FKs to other tables which contain things I might need to keep notes on.
• EnteredByUsedID - FK to tblUser of the database user who entered the note onto the system.
• EntryDate - Date note was entered
• NoteText - the text of the note
• CommTaskID - Explained later - see below

This way, you'll know which table it belongs to, and which record in that table that it belongs to.

In a query, you can simply join TablePK to the key in your table, and filter TableID based on what table you're joining to.

Or, if you need to set up a relationship you can simply add a new field to all your tables with their TableID in it.

这篇关于Notes系统在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！