需要有关在库存管理应用程序中设置表关系的位置的建议 [英] Need advice on where to set the table relationship in my inventory management application

问题描述

有一个MS Access数据库，我打算用它来存储我的库存数据。我正在尝试构建一个vb.net应用程序，我很困惑在哪里指定表关系，因为Product-Category-SubCategory之间存在一些关系......



我可以看到有两种方法可以做到这一点，第一种方法是在MS Access本身进行，另一种方法是在Visual Studio中的数据集中。问题是我不知道哪个是明智的选择，因为我是vb.net的新手。



有人可以就此事给我一个建议。？



我试过的：



我已尝试在两个地方设置并且似乎正在工作，但仍不确定它是否是正确的方法。

There is an MS Access database which i'm planning to use to store my inventory data. I am trying to built an vb.net App and I'm confused about where to specify table relation as there are some relation between Product-Category-SubCategory so and so..

As I can see there are two ways to do it, First one is to do it in MS Access itself and the other is within the Dataset in Visual studio. Problem is that i don't know which would be a sensible choice as I am new to vb.net.

Can somebody give me an advice on this matter.?

What I have tried:

I have tried setting up on both places and seems like working, But still not sure if it is the right method.

推荐答案

我的一般建议是在数据库中设置关系，并使用该关系的 View 公开给任何客户端程序。



创建视图-Anweisung（Microsoft Access SQL） Microsoft Docs [ ^ ]



此设计让您可以在以后自由更改数据库（理想情况下） ，如果你想进行设计更改，你只需调整查看，而无需更改任何代码。



更有可能的是，您最终不得不关心客户端代码中的某些关系，但不要在 1 NF 级别上执行此操作，因为因为您的客户端代码将在您更改数据库中的某些内容时进行更改，因此会适得其反。

My general advice is to set the relationships in the database and use a View of that relationship to be exposed to any client program.

CREATE VIEW-Anweisung (Microsoft Access SQL) | Microsoft Docs[^]

This design gives you the freedom to change your database later on (ideally, you would just have to adjust the View if you want to do a design change) without always having to change any code.

More likely than not, you will end up having to care for some relationships in your client code but don't do this on an 1 NF level because that will be counter productive since your client code will ahve to change any time you change something in your database.

如果您的意思是外键关系，那么最好的位置在DB中，因为那时候它可以强制执行参考完整性 - 一种说确保dtaa全部有效的奇特方式



建立外键时两个表之间的关系，数据库将为您强制执行参考完整性：

发票

If you mean Foreign Key relationships, then the best place is in the DB, because then it can enforce Referential Integrity - a fancy way of saying "make sure the dtaa is all valid"

When you establish a Foreign Key relationship between two tables, the database will enforce Referential Integrity for you:
Invoices

ID, Date, Customer, Total

InvoiceLines

InvoiceLines

ID, InvoiceID as Foreign Key to Invoices.ID, Product, Quantity, PricePerItem

因此，每个发票都有许多InvoiceLines，这是您期望的。

数据库将阻止您添加InvoiceLine行，除非InvoiceID在发票表中有匹配的行，并且会阻止您删除行在发票表中，InvoiceLines表中存在引用它的任何行：确保参考完整性。



您可以在代码中执行此操作，是 - 但是总有一天你错过了一些东西，当这种情况发生时，它就变成了一个真正的PITA来修复你的数据，因为你最终得到了你不知道它与之相关的浮动数据。

So you have many InvoiceLines per Invoice, which you would expect.
The DB will prevent you adding an InvoiceLine row unless the InvoiceID has a matching row in the Invoices Table, and will prevent you deleting a row in the Invoices table while any row exists in the InvoiceLines table that refers to it: Referential Integrity is assured.

You can do that in your code, yes - but there is always a chance that you miss something one day, and when that happens it becomes a real PITA to fix your data because you end up with floating data that you no long know what it is related to.

这篇关于需要有关在库存管理应用程序中设置表关系的位置的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！