SQL数据库设计中使用商店信用
考虑这两个用例。
假设一家商店为顾客提供商店积分。商店信用来自退款、折扣、多付金额等...
客户决定如何以及何时 使用此信用额。例如说客户A 拥有 50 美元的商店积分，并且 他决定使用 25 美元作为发票 A 发票 B 为 25 美元...或决定 获得商店信用 在那里反对他的未来账户 购买。
顾客和商店还必须 有办法查找总数 商店信用。
在表结构中满足此要求的最简单、最直接的方法是什么？
我目前有表：
- 交易（用于日记帐和报告目的）
- 客户
- 信用
- 使用的信用
发票表将具有其他关联，例如发票行、收据、收据行等...
这是最好的方法吗？我主要关心的是报告和交易机制的便利性。
或者，我正在考虑仅保存交易表内的所有信用条目和已使用的信用。
更新了图表以进行澄清和输入
Consider these two use cases.
Say a store gives out store credit for customers. Store credit come from refunds, discounts, overpaying amounts etc...
The customer decides how and when to
use this credit. E.g. Say customer A
have $50 dollars in store credit and
he decides to use $25 for invoice A
and $25 for invoice B... or decides
to have the store credit sitting
there against his account for future
purchases.The customer and store must also
have a way to look up the total of
store credit.
What is the easiest and most straight forward way to cater for this in table structure?
I currently have tables:
- Transaction (For journal and reporting purpose)
- Customer
- Credit
- Used Credit
Invoice table will have other associations like invoice lines, receipt, receipt lines etc...
Is this the best way to do this? My concern is primarily regarding ease of reporting and transaction mechanics.
Alternatively I'm considering just saving all credit entry and used credit inside the transaction table.
Updated diagram for clarification and from inputs
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我错了请纠正我，但我相信你想要这样的东西。
Customer具有CreditEntry，而CreditEntry具有UsedCredit。还有一些Transaction，其中包含相应Invoice的链接。最后，Transaction具有指向此交易期间使用的许多UsedCredit的链接。如果一次使用了所有 CreditEntry，则此Transaction将使用一个UsedCredit，并且此UsedCredit将对应于整个 < code>CreditEntry这里的数据库模式非常简单 - 通过外键建立关系。您可能希望创建一个额外的
唯一约束来反映交易和发票之间的一对一关系哦，顺便说一句，您用于创建图表的 服务 很酷。
Correct me if I'm wrong, but I believe you want something like this.
A
CustomerhasCreditEntrys, andCreditEntrys haveUsedCredites. There are alsoTransactions which include links to correspondingInvoice. And finally, aTransactionhas links to manyUsedCreditwhich were used during this transaction. If all CreditEntry was used at a time, then there will be oneUsedCreditused with thisTransaction, and thisUsedCreditwill correspond to the entireCreditEntryThe database schema would be quite straightforward here - relations through foreign keys. You would probably want to create an additional
uniqueconstraint to reflectONE-TO-ONErelation betweenTransactionandInvoiceOh, by the way, that service that you used for diagram creation is cool.
创建 4 个表，其中 1 个用于客户，另一个用于其信用和已使用的信用，然后另一个用于交易和发票。您可以加入交易和发票，因为它们可能具有相似的数据。
create 4 tables, 1 for customers then another for their credit and used credit and then another for transaction and invoice. You may join transaction and invoice since they may have similar data.