Your design commonly looks good except few mistakes.
1) You had used a wrong field in order to establish a relation between ProductCategory and Product. You have to use ProductCategoryID in Product table instead of using ProductCategoryName. Howeever, some products may belong to more than one product category.
If this is a valid case for your project, you should add additional table something like "ProductVsProductCategory" which will allow you to establish a "many to many" relation between Product and ProductCategory tables if you intend to add some products into multiple categories.
Basic structure of ProductVsProductCategory might look like following:
ProductVsProductCategoryID (primary key)
ProductID (refers to Product table)
ProductCategoryID (refers to ProductCategory table)
2) OrderProductName field in the OrderDetail table is unnecessary. You have a relation to Produt table and you can access ProductName from there anyway. You can cancel it.
3) OrderNumber in the Order table is unnecessary if it is not a special value rather than unique identity number. You can use OrderID field as an identity of the order.
4) Similary, OrderQuantity and OrderTotalPrice fields might be unnecessary if you have not special reason to place them there. You can calculate order quantity and total price values by means of using basic sql statements that sums related OrderDetail rows.
There are many additional tables can be added depending on your project requirements. I also recommend you to follow MSDN articles and specially CodeProject in order to improve your abstraction skills.
You obviously put some thought into this design. But you would regret that you have to search for codes in two tables. And what if you want to add details for codes?
I suggest to store all codes in the same table like this:
CREATE TABLE payment (
idpayment serial PRIMARY KEY
-- more payment details?
);
CREATE TABLE payment_code (
idpayment_code serial PRIMARY KEY
, idpayment int NOT NULL REFERENCES payment
, idthingC int REFERENCES thingC -- can be NULL
, idthingD int REFERENCES thingD -- can be NULL
, c1 int NOT NULL
, c2 int NOT NULL
, c3 int NOT NULL
, CONSTRAINT either_c_or_d -- reference either to c or to d
CHECK (idthingC IS NULL AND idthingD IS NOT NULL
OR idthingD IS NULL AND idthingC IS NOT NULL)
, CONSTRAINT c_distinct_per_payment -- distinct idthingC per payment (?)
UNIQUE (idpayment, idthingC)
);
CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE idthingD IS NOT NULL; -- only one idthingD per payment
(Depending on the nature of the codes, maybe even just 1 code per row.)
The partial unique index idx_one_d_per_idpayment disallows more than one reference to thingD per payment.
This allows at most 1 thingD per payment and any number of distinct thingC.
Mutually exclusive payment types
You later clarified, those are mutually exclusive.
This allows at most 1 thingD per payment or any number of distinct thingC (but not both):
CREATE TABLE payment (
idpayment serial PRIMARY KEY
, type_cd "char" NOT NULL DEFAULT 'C' -- optional default
-- more payment details?
, CONSTRAINT type_cd_valid CHECK (type_cd = 'C' OR type_cd = 'D')
, CONSTRAINT redundant_uni_for_fk_constraint UNIQUE (idpayment, type_cd)
);
CREATE TABLE payment_code (
idpayment_code serial PRIMARY KEY
, idpayment int NOT NULL -- multicolumn ...
, type_cd "char" NOT NULL -- ... FK reference
, idthingC int REFERENCES thingC
, idthingD int REFERENCES thingD
, c1 int NOT NULL
, c2 int NOT NULL
, c3 int NOT NULL
, CONSTRAINT either_c_or_d
CHECK (type_cd = 'C' AND idthingC IS NOT NULL
OR type_cd = 'D' AND idthingD IS NOT NULL)
, CONSTRAINT c_distinct_per_payment
UNIQUE (idpayment, idthingC)
, CONSTRAINT payment_c_or_d
FOREIGN KEY (idpayment, type_cd) REFERENCES payment(idpayment, type_cd)
);
CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE type_cd = 'D'; -- slightly simpler now
To enforce your rules, add a type column in table payment (I named it type_cd since "type" is too generic, and I chose data type "char" - with double quotes - efficient for tiny enumerations). Include that column in the FK reference (redundantly). This allows a CHECK constraint (either_c_or_d) in table payment_code to enforce valid references.
The UNIQUE constraint redundant_uni_for_fk_constraint seems redundant, but is required for the multicolumn FK reference on (idpayment, type_cd). Related, with more explanation:
Both FK columns (idpayment, type_cd) must be NOT NULL to enforce referential integrity. Or (if you need rows without assigned payment) declare the FK constraint as MATCH FULL. Details:
Best Answer
I find with financial transactions you want to have a detailed audit trail of every movement of funds. This means a write-once (no updates) table containing fund movements using a double entry accounting model.
In a simple version of this, you would have one table containing the list of payees and payers (your senders, administrators and receivers). Then you would have another table (or better two) that contain the financial transactions. The tables are roughly as follows:
PARTY: Name, address, bank account, other details about payers/payees.
TRANSACTION: Date, transaction ID, order reference, other general info.
TRANSACTION-DETAIL: FK to Transaction, FK to party, Amount.
When someone makes a simple payment you record one
TRANSACTIONand twoTRANSACTION-DETAILrecords. The first record shows who paid and the second shows who was paid. The sum of the amounts for the two details must be zero, that means you need to pick a convention for positive and negative Amounts such that one means giving money and the other means getting money.If the transaction is more complicated, you just add more
TRANSACTION-DETAILrecords, for example instead of one person getting paid, you get two people being paid (administrator and seller for example).While the sum of transaction detail amounts for any transaction is zero, the sum of amounts for any given
PARTYmay be non-zero, meaning you have some of their money in your system. To clear these amounts, you need aPARTYrepresenting any payment settlement organization or bank that you deal with. These are the ultimate sources and sinks of funds and their balances don't need to be zero in the long run.All of this may seem complicated, especially compared to what you were thinking of, but the advantage of it is that it allows you to keep a detailed record of exactly what happened and when it happened. If you need to make an update, i.e. cancel and order or issue a credit note, just add more transactions and transaction details. You can always go back and see exactly what went on. This convention is how people have been handling and accounting for financial transactions for hundreds of years, so if nothing else, people will understand what you're doing.
Here is an example with some data. In this example, Buyer X pays 100.00 for an order. The Agency collects 10.00 and pays Seller 1 40.00 and Seller 2 50.00. They buyer pays with PayPal and the Agency and both Sellers deposit their money in one of two banks. Note that this example is quite simple insofar as the agency and the sellers clear out their accounts after just one order. In a real-world scenario, payments for many orders might be batched up before settlement is made.
Note that for OP's scenario where determination of the seller may be delayed, the agency would need to have some kind of escrow account. This would be an additional party, such as "Outstanding Order Payments". In such a case the agency could take their fee right away and the balance would be placed in the escrow account. When the sellers are determined later on, they would be paid out of the escrow account, rather than out of the buyer's account, as is shown in the example, above.
Also note that while each transaction is shown with two details above, it is also legitimate to have three or more details for one transaction if you happen to want to record it that way. For example, all of the details in 8001, 8002 and 8003 could have been represented as a single transaction if you prefer.