小型 CRM/发票系统的数据库设计
我目前正在为我的客户开发一个小型客户关系和发票管理系统。我遇到了一些小问题，我想讨论一下。
围绕订单、客户和产品的最佳实践是什么？我的客户是否可以删除订单、客户和产品？
目前，我围绕订单、客户和产品之间的关系原则设计了数据库，如下所示：
客户
身份证号码
姓名
...
产品
身份证号码
姓名
价格
...
订单
身份证号码
客户ID
订单日期
...
订单行
身份证号码
订单ID
ProductID
像这样我可以连接所有不同的表。但是，如果我的客户删除了一个产品，当他后来打开几个月前创建的包含该产品的订单时会发生什么。它将消失，因为它已被删除。对于客户来说也是如此。
单击删除按钮时我是否应该禁用产品和客户，或者最佳实践是什么？
如果我说每当我的客户决定删除某个产品时就将其禁用，那么如果他稍后尝试添加与已禁用产品具有相同产品 ID 的新产品，会发生什么情况，我是否应该再次启用该项目？
请分享你的智慧:D
I'm currently developing a small customer relationship and invoice management system for my client. And I have run into some small issues which I would like do discuss.
What is the best practice around orders, customers and products. Should my client be able to delete orders, customers and products?
Currently I have designed my database around the principle of relationships between order, customer and product like this:
Customer
ID
Name
...
Product
ID
Name
Price
...
Order
ID
CustomerID
OrderDate
...
Order Line
ID
OrderID
ProductID
Like this I can connect all the different tables. But what if my client delete a product, what happens when he later open a order he created months ago which had that item in it. It would be gone, since it has been deleted. Same goes for customer.
Should I just disable the products and customers when the delete button is clicked or what is the best practice?
If I lets say diable a product whenever my client decides to delete it, what happens then if he later tries to add a new product with the same product ID as a disabled product, should I just enable that item again?
Please share your wisdom :D
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不会“删除”，而是为 IsActive 添加一个布尔列。这样您就不会丢失历史数据。例如，如果他们能够删除客户，那么他们将无法查看有关该客户的历史记录，这可能会使查看统计数据变得困难或不可能。对于订单表，您可以有一个表示“当前”、“已取消”、“已填充”之类的列来完成相同的操作。该列应该是查找/代码表的代码。
Instead of "deleting" I would add a boolean column for IsActive. That way you won't loose historical data. For instance, if they are able to delete a customer then they won't be able to look at history regarding that customer and it may make looking at statistical data hard or impossible. For the order table you could have a column that represents something like "current", "canceled", "filled" to accomplish the same thing. That column should be a code to a lookup/codetable.
“如果我说每当我的客户决定删除某个产品时就将其禁用，那么如果他稍后尝试添加与已禁用产品具有相同产品 ID 的新产品，会发生什么情况，我是否应该再次启用该项目？”
完全取决于您的业务场景 - 客户当前维护它的方式有何独特之处？ （说手动？）当一个早前停产的旧产品突然重新出现时，他们如何处理？ （他们是将其视为新产品还是开始引用旧产品？）我想这些问题没有正确或错误的答案，这取决于功能 - 始终建议了解现有流程（减去软件） ）已经受到客户关注，然后将其映射到软件功能。
例如。您始终可以添加“具有此代码的产品已存在 - 您想使用它而不是创建新产品吗？”一种消息。此外，您在表中用作外键的产品 ID 和用于向客户展示的产品 ID 最好是不同的 - 您不想将它们混淆。
"If I lets say diable a product whenever my client decides to delete it, what happens then if he later tries to add a new product with the same product ID as a disabled product, should I just enable that item again?"
Depends entirely on your business scenario - what is unique in the way customers maintain it currently? (say manually?) How do they handle when an old product which was earlier discontinued suddenly reappears? (Do they treat it as a new product or start referring to the old product?) I guess there are no right or wrong answers to these questions, it depends on the functionality - it is always advisable to understand the existing processes (minus the software) already followed by the customers and then map them to the software functionality.
For eg. you could always add a 'A product with this code already exists - do you want to use that instead of creating a new one?' kind of a message. Also, the product ids that you use in your tables as foreign keys, and the ones that you use to show the customer, better be different - you dont want to get them mixed up.
为什么您希望能够删除订单？我认为这样的系统会锁定订单，以便您知道自己拥有良好的历史记录。客户也是如此，为什么要删除他们呢？也许是一种“存档”它们的方法，必须设置一些标志，这样它们就不会出现在客户列表或其他东西上。
至于禁用然后输入具有相同产品 ID 的新项目 - 我也不知道你为什么要这样做，每个产品 ID 都是唯一的，即使你停止使用某个产品，它也应该保留它的产品 ID所以你有一个记录。但如果您必须这样做，那么您可以在业务规则中添加一个约束，其效果是“如果没有具有此产品 ID 的活动产品，则允许它。如果我们有一个活动的产品并且它具有相同的产品 ID，然后抛出错误。”因此，您只允许一个具有该产品 ID 的活动产品 - 但老实说，我认为这会令人困惑，并且在后端，您将需要使用一个对于每个产品都不变的唯一 ID 来在表之间进行链接。
Why would you want to be able to delete orders? I would think that such a system would lock orders so that you know you have a good history. Same goes for customers, why delete them? Perhaps a way to "archive" them, having to set some flag, that way they don't show up on customer lists or something.
As for disabling and then entering a new item with the same product ID - I'm not sure why you'd do that either, each product ID is unique for a reason, even if you discontinue a product, it should keep it's product ID so you have a record. But if you must, then you could put a constraint in the business rules, something to the effect of "If there is no product that is active with this product ID then allow it. If we have a product that is active and it has the same product ID, then throw an error." Thus, you only allow for one active product with that product ID - but honestly, I think this would be confusing, and on the back end you'll want to use a unique id that is unchanging for each product to link between tables.