基于信用的购买的数据库设计
我需要一种优雅的方式来为在线商店实现基于信用的购买，该商店的产品种类较少，可以使用虚拟信用或真实货币购买强>。或者，产品只能以积分定价。
之前的工作中
，我在使用不同的产品类型（例如，Credit、Voucher 或 Music）之前实施了基于信用的购买，并通过订单后处理来实现以真实货币的形式将购买的积分分配给用户，随后可用于对未来订单的费用总额进行折扣。
作为一个临时解决方案，这相当有效，但没有成功地将虚拟货币与真实货币分开，这正是我想做的，因为对于客户来说，消费积分在心理上比消费真实货币更容易。
设计
我需要正确设计数据库的指导，并支持同时以折扣价批量购买积分和真实货币产品。或者，是否应该所有产品都以积分定价，并且只有积分具有实际货币价值？
现有数据库设计
部分Products表：
- ProductId
- Title
- Type
- UnitPrice
- SalePrice
部分Orders表：
- OrderId
- UserId（与
Users表相关，未显示） - Status
- Value
- Total
Partial OrderItems表（类似于CartItems表）：
- OrderItemId
- OrderId（相关到
Orders表） - ProductId（与
Products表相关） - 数量
- 单价
- 销售价格
预期UserCredits 表：
- CreditId
- UserId（与
Users表相关，未显示） - Value（+/- 值。随时间求和以确定 saldo。）
- 日期
我在 SQL Server 数据库上使用 ASP.NET MVC 和 LINQ-to-SQL。
I need an elegant way to implement credit-based purchases for an online store with a small variety of products which can be purchased using virtual credit or real currency. Alternatively, products could only be priced in credits.
Previous work
I have implemented credit-based purchasing before using different product types (eg. Credit, Voucher or Music) with post-order processing to assign purchased credit to users in the form of real currency, which could subsequently be used to discount future orders' charge totals.
This worked fairly well as a makeshift solution, but did not succeed in disconnecting the virtual currency from the real currency, which is what I'd like to do, since spending credits is psychologically easier for customers than spending real currency.
Design
I need guidance on designing the database correctly with support for the simultaneous bulk purchase of credits at a discount along with real currency products. Alternatively, should all products be priced in credits and only credit have a real currency value?
Existing Database Design
Partial Products table:
- ProductId
- Title
- Type
- UnitPrice
- SalePrice
Partial Orders table:
- OrderId
- UserId (related to
Userstable, not shown) - Status
- Value
- Total
Partial OrderItems table (similar to CartItems table):
- OrderItemId
- OrderId (related to
Orderstable) - ProductId (related to
Productstable) - Quantity
- UnitPrice
- SalePrice
Prospective UserCredits table:
- CreditId
- UserId (related to
Userstable, not shown) - Value (+/- value. Summed over time to determine saldo.)
- Date
I'm using ASP.NET MVC and LINQ-to-SQL on a SQL Server database.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不会用积分向供应商付款，因此从内部会计角度来看，最好仅针对您的产品持有硬现金价值（美元、英镑等）。
向网站用户展示时，您需要一个货币换算表，将硬价格转换为优惠券。将数据模型与前端显示分离是一个关键的设计策略。无论您决定显示真实现金价格和虚拟信用价格，还是仅显示信用评估，都应该与数据在数据库中的存储方式无关。
You don't pay your suppliers in Credits, so from an internal accounting perspective it would be better to hold only hard cash values (USD, UKP, whatever) against your products.
When displaying to your site's users you need a currency conversion table which translates hard prices into vouchers. Separating the data model from the front end display is a key design strategy. Whether you decide to show both real cash price and virtual credit price or just the credit valuation should have no bearing on how the data is stored in the database.
与您的会计/财务人员交谈。信用一旦给予，就与双重会计中的任何金钱一样真实，确实，您不是用信用向供应商付款，但您承诺以信用交付货物，而您必须用现金支付。它应该始终保持平衡，并且要查看您的完整财务状况，您应该能够在资产负债表中获得这些贷项。
尝试从一开始就对帐户和交易进行建模 - 稍后连接到财务/会计软件会更容易，并且如果您可以在双方上运行报告以进行对账，那么总是好的。
Talk to your accounting/finance people. Credit, once given, is as real as any money in double accounting, true you are not paying your suppliers in credits, but you are promising to deliver goods for credits that you will have to pay in hard cash. It should all balance at all times, and to see your complete financial position you should be able to get these credits in your balance sheet.
Try to model accounts and transactions, as well, right from the start - it will be easier to connect to finance/accounting software later and it is always good if you can run reports on both sides for purposes of reconciliation.