Transaction table design

[kau14]

Hi,

I need data table desgin for transaction table.

I want transaction history for particular client having unique client_id but many having many transaction id's.

transactions relate to credit, debit, transactor name and date.

Regards

Kaustubh

[pritaeas]

What exactly do you need help with?

[kau14]

hi, thanks for reply

I need is, what should be the table desgin so that am able to retrive it easily,

i want to retrive transaction history of particular client based on the transaction id.

suppose client x is having cl_id = 1
has paid some amount for transaction id =1

then credit should shown in front of that tid,
after some time expenses done for that client against that particulat transaction
then need debit entry for that transaction having transaction id =1.

like wise same client can make many transaction, will have diffrent transaction id's have to retrive all data eaisly....

am having the basic sql knwledge..plz explain if possible.

Regards

Kaustubh

[ChrisHunter]

Sorry if this isn't exactly what you're asking for but your English isn't the greatest so I may have miss understood you a little but here goes...

It depends on how simple you want to keep it.

If the client can only purchase one type of item/service at a time (but multiple of that one item/Service) You will need at least 3 tables:

Client Table
- Should have a well named primary key such as ClientID.
- Used to store client information.

Products table (or what ever the client is paying for)
- Should have a well named primary key such as ProductID.
- Used to store product or service information.

Transation Table (or orders table)
- Should have a well named primary key such as ProductID.
- Used to store the ID of both the product purchased and the client (as forgien keys).
- It's also important to have fields such as quantity, price per unit, total price and order date. This is because you can keep a record of what price the item was at the time of order (you might want to change prices at some point).

Then when you want to retireve all transations for a customer you simply select the fields needed using the client ID and the same can be done to see which customers have bought a particular product.

Hope this helps...

[ryanjayson]

Try this dude

CLIENT TABLE
Columns 
    ClientID(PK), 
    Name, 
    Address,
    Contact,
    (Personal Info of the client/Etc)

TRANSACTION TABLE
Columns
    TransactionID(PK), 
    ClientID(FK), 
    TransactionType, 
    TransactionDate,
    (Fields that are related to transaction/Etc)

Relationship of the tables are one:many, every client can have many transaction. If you query the transaction use table join

SELECT T.TransactionID, C.Name, T.TransactionType, T.TransactionDate 
FROM TransactionTable T 
INNER JOIN CustomerTable C 
ON T.ClientID = C.ClientID

something like that. Hope you get the idea and will helps you.