限制数据输入 [英] Restricting Data Entry

问题描述

我有2张桌子

tblFunds

FundID（自动/ PK）

FundNo（文字）< br $>
FundDescr（正文）

tblGrants

GrantID（自动/ PK）

GrantNo（文字）

GrantDescr（文字）

问题1：我有第3张表，其中包括自己的字段和字段从其他表。现在我想在第3张表中包含FundNo和GrantNo。拨款号码可以有多个基金号码。我想限制数据进入第3表，因为它不允许不正确的数据。例如，Fund 872属于Grant Number A1。如果有人选择Grant A74作为资金872，数据库将导致错误消息。我该怎么做呢？

问题2：我是否需要将GrantID作为附加字段添加到tblFunds？

问题3 ：我是否使用以下字段创建另一个名为tblFundGrants的表：

tblFundGrants

RecordID（PK / auto）

FundID （FK）

GrantID（FK）

问题4：这是棘手的部分。一个基金可以有一个以上的拨款号码。


任何帮助？谢谢。

I have 2 tables

tblFunds
FundID (auto / PK)
FundNo (Text)
FundDescr (Text)


tblGrants
GrantID (auto / PK)
GrantNo (Text)
GrantDescr (Text)

Question 1: I have a 3rd table which includes fields of its own plus fields from other tables. Now I want to include FundNo and GrantNo in this 3rd table. A Grant Number can have more than one Fund numbers. I would like to restrict data entry into the 3rd table as such that it won''t allow incorrect data. For example, Fund 872 belongs to Grant Number A1. If someone picks Grant A74 for fund 872 the DB will result in an error message. How do I do this?

Question 2: Do I need to add GrantID as an additional field to the tblFunds?

Question 3: Do I create another table called tblFundGrants with these fields:

tblFundGrants
RecordID (PK / auto)
FundID (FK)
GrantID (FK)

Question 4: Here is the tricky part. One fund can have more than one Grant Number.

Any help any one? Thanks.

推荐答案

你读过表格规范化吗？

是的，我有。实际上相当多次。但是，我是那些主要从听力学习的人之一。换句话说，对我来说，有效的学习不是单独阅读。如果我能看到/听到例子，它会有很大帮助。


如果您没有时间通过​​我发布的表格示例来解释一下，我会理解。


谢谢。

Yes, I have. Actually quite a few times. However, I am one of those who learn mostly from listening. In other words, for me effective learning does not come from reading alone. It helps tremendously if I can see/hear examples.

If you don''t have time to explain a little by taking the examples of tables I posted, I will understand.

Thanks.

我会非常乐意尝试和帮助我只是觉得这可能会有所帮助。听起来好像你有很多类型的问题。


Fund1 = Gant1

Fund1 = Gant2


Gant1 = Fund1

Gant1 = Fund2


我错了吗？


引自Mary''s文章。

I would be more than happy to try and help I just thought that may assist. It sounds as though you have a many to many type of issue.

Fund1 = Gant1
Fund1 = Gant2

Gant1 = Fund1
Gant1 = Fund2

Am I mistaken?

Quoting from Mary''s article.

如果任何表有多对多关系，则必须使用JOIN表进行分解。例如，客户可以拥有许多供应商，供应商可以为许多客户提供服务。这被称为多对多的关系。您需要创建一个JOIN表，该表具有一个主键，该主键由对Customers表的外键引用和对Suppliers表的外键引用组成。因此，SuppliersPerCustomer表将是{SupplierID，CustomerID}。现在，Suppliers表与SuppliersPerCustomer表具有1对多的关系，Customers表也与SuppliersPerCustomer表具有1对多的关系。

If any tables have a many to many relationship this must be broken out using a JOIN table. For example, Customers can have many Suppliers and Suppliers can supply to many Customers. This is known as a many to many relationship. You would need to create a JOIN table that would have a primary key made up of a foreign key reference to the Customers table and a foreign key reference to the suppliers table. Therefore the SuppliersPerCustomer table would be {SupplierID, CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCustomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCustomer table.

有很多方法可以解决这个问题1创建一个可以保存表之间各种关系的表格。


GrantFundTbl

GFID（PK / auto）

GFrelation


FundID 1 = Gant1

FundID 1 = Gant2

FundID 2 = Gant2


这是您要找的？

There are many ways this can be broken out 1 would be to create a table that would hold the various relationships between the tables.

GrantFundTbl
GFID (PK / auto)
GFrelation

FundID 1 = Gant1
FundID 1 = Gant2
FundID 2 = Gant2

Is this what you are looking for?

这篇关于限制数据输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！