Hi guys,

I am having a bit of a trouble deciding what is the proper way to store a list of transactions. Is it possible to store transaction using only a relationship? I don't really see how, since if i had the same item sold twice by the same salesperson and same client that would create an impossible operation.

Say that i want to sell Pen model 1 by Salesperson 1 to Client 1. I would have

ClientID, ProdID, EmpID
1,1,1

Then that same person comes in the next day and buys another Pen model 1 from the same salesperson i would have

ClientID, ProdID, EmpID
1,1,1

Should i create a Transaction entity? That stores all records, or is my ProdID flawed to begin with since it uses the Same prodID independent of the instance of the Pen model?

i.e. Should I make each Item unique, even if its identical (like all pen model 1 instances have a unique ID), or should each transaction be unique?

Cheers,

A good database design give you no problem of inserting, updating and deleting with data integrity. if you need additional benefits from your database you will find out on DBMS programs.
To response your question on database design issue, you could consider a sold pen as a unique one if you have no returned good policy. That will make sense to have an ID for individual pen and to use it as primary key. Then uses Sale ID,Pin ID and Customer ID as secondary key to response thoes details when querying. You can also have data constraint to force secondary key fields not to be empty so that you can have links to thoes detail when you do a query.

Ordinarily for transactional systems you will have an order header (the date of a transaction, client, salesperson etc.) and relate that to order detail (sometime order line) that contains the details of the items purchased (product id, discounts, quantity etc.).

So to answer your question, you decompose your table into two and the missing part of your key is the time of the sale.