回归本源 [英] Back to Basics

问题描述

我似乎把自己编程到太多角落，所以我试图理解一些基本的想法。我不是要求确切的代码，而是要求方向和访问逻辑。


对于测试数据库，我创建了两个表并定义了关系

tAAA ：

展开 | 选择 | Wrap | 行号

解决方案

@OldBirdman


Hello OldBirdman，场景，你描述的是，在父表和子表之间执行参照完整性是这样的没有机会创造'孤儿''记录。用于强制执行RI的简单事件链是：

1. 将记录添加到父表。
2. 保存父项中先前添加的记录表。
3. 现在，只有现在，您可以使用外键值字段值等于父表的主键值来向子表添加/保存记录。
4. 这对你有意义吗？

是的，这是有道理的，但它会让事情变得困难。在我的实际程序中，我将通过将网页复制/粘贴分离到程序来创建两个部分。在一切都到位之前，我不能问用户是否要中止新父母和孩子的创建。

你说我必须保存父母，然后确定我是否还有孩子。也许我没有，或者我得到的是重复，或者太多是无效的，或者信息是不完整的，无论是父母还是孩子。

这说我应该保留Key = 1作为现有的父母。我可以：

1）在此记录中创建我的新父母

2）创建一个孩子，链接到Key = 1

3）如果中止（不要保留任何东西），我取消了孩子。父母可以重复使用。

4）如果不是Abort，我会将新数据移动到新记录中。这不是一件容易的事，涉及到记录的所有字段的循环。

5）保存新的记录

6）更改外键（FK）孩子到新的记录。


- 或 -


1）创建我的新父母

2 ）保存父母

3）创建孩子

4）如果中止，UnDo孩子和删除父母 - 可能会导致顺序键中的漏洞

5如果不是中止，我已经完成了。


这看起来很复杂，似乎很常见。我打电话给酒店，并尝试预订。他们得到我的名字，并问日期。我说星期六，他们说好，我也说下周一（但不是星期天），他们不能这样做。我说谢谢你！并挂断电话。他们创建了一个父母和一个孩子，都要删除或撤消？我想。


那么 - 与取消保存或UnDo相比，保存记录然后删除它的处罚是什么？

< br>

这就是为什么好的数据库设计如此重要。


但是在你的酒店示例中没有创建记录。

你会（可能）有

a桌子储藏室

和一张存放日历的桌子

和一张存放客人的桌子并将它们连接到一个房间和日历


在房间预订过程中，您只在第三个表格中创建记录

，访问权限仅在您在预订中离开时创建该记录制作表格。

取消预订的过程中途。

数据库操作员取消记录并且不会创建任何记录。


现在您的实际情况可能与酒店情景有所不同。

在这种情况下，您需要为您设计绕过这样的困难。

最好提供一下你的实际场景描述

就像你为酒店做的那样，我们可以试着帮助你。 />

PS

谁说数据库设计不是一个复杂的过程？ ：）

I seem to program myself into too many corners, so I''m trying to understand a few basic ideas. I''m not asking for exact code, but for direction and Access logic.

For a test DB, I created two tables and defined relationships
tAAA:

Expand|Select|Wrap|Line Numbers

解决方案

@OldBirdman
Hello OldBirdman, the scenario, which you describe, is the enforcing of Referential Integrity between a Parent and Child Table so there will be no chance that ''Orphaned'' Records will be created. The simple Chain of Events to enforce RI is:

1. ADD a Record to the Parent Table.
2. SAVE the previously added Record in the Parent Table.
3. Now, and only now, can you ADD/SAVE a Record to the Child Table with a Foreign Key Field Value equal to the Primary Key Value of the Parent Table.
4. Does this make sense to you?

Yes, it makes sense, but it is going to make things difficult. In my actual program, I will be creating both pieces by picking apart a webpage copy/paste to the program. Until everything is in place, I can''t ask the user if he wants to abort the creation of the new parent AND child.
You''re saying I have to save the parent, and then determine if I even got the child. Maybe I didn''t, or what I got is a duplicate, or too much is invalid, or information is incomplete, either with the parent or the child.
This says I should reserve Key=1 as an existing parent. I could:
1) Create my new parent in this record
2) Create a child, linked to Key=1
3) If Abort (don''t keep anything), I cancel the child. The parent is there for re-use.
4) If not Abort, I move the new data to a new record. This is not an easy task, involving loops thru all the fields of the record.
5) Save the newnew record
6) Change the foreign key (FK) of the child to the newnew record.

--OR--

1) Create my new parent
2) Save parent
3) Create the child
4) If Abort, UnDo child and delete parent - may cause holes in sequential keys
5) If not Abort, I''m done.

This all seems so complex for what seems like it would be very common. I call a hotel, and try to make a reservation. They get my name, and ask dates. I say Saturday, and they say OK, and I say also the following Monday (but not Sunday), and they can''t do. I say "Thank You!" and hang up. They''ve created a parent, and one child, both to be deleted, or undone? I guess.

So - what, if any, are the penalties for saving a record and then deleting it, as compared to cancelling the save, or an UnDo?

Thats why good database design is so important.

But in your hotel example no record would have been created.
You would (possibly) have
a table storing rooms
and a table storing a calendar
and a table storing a guest and linking them to a room and the calendar

During the room reservation process you are only creating records
in the third table and access only creates that record when you navigate away from it in the reservation making form.
Halfway through the process you cancel the reservation.
The database operator cancels the record and no record ever gets created.

Now your actual scenario may be different to the hotel scenario.
In that case you need to design your way around such difficulties.
It would be better to provide a description of your actual scenario
like you did for the hotel and we can try and help you.

PS
Whoever said database design wasn''t a complex process? :)

这篇关于回归本源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！