Database design for credit based purchases
Posted
by FreshCode
on Stack Overflow
See other posts from Stack Overflow
or by FreshCode
Published on 2010-03-30T10:47:23Z
Indexed on
2010/03/30
10:53 UTC
Read the original article
Hit count: 422
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
Users
table, not shown) - Status
- Value
- Total
Partial OrderItems
table (similar to CartItems
table):
- OrderItemId
- OrderId (related to
Orders
table) - ProductId (related to
Products
table) - Quantity
- UnitPrice
- SalePrice
Prospective UserCredits
table:
- CreditId
- UserId (related to
Users
table, 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.
© Stack Overflow or respective owner