Database design for credit based purchases

Question

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.

Answer 1

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.

Answer 2

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.