Hey, everyone!

There is a user. User has items. Users can exchange items with each other. I need to track this exchanges the following way.

Say, I have three users: user #1, user #2, and user #3.
Initially, #1 has an item A, #2 has an item B, and #3 has an item C.

First exchange occurs. #1 gets B from #2 for an offer of A. This moment, we have: #1 has B, #2 has A, #3 has C.

Seconds exchange occurs. #1 gets C from #3 for an offer of B. That is, now #1 has C, #2 has A, and #3 has B.

Now I need to have an ability to restore the whole chain of exchanges for each user. For example, for #1 that would be: A->B->C.

Any suggestions?
Thanks in advance.

Each object can only be owned by one user at a time, I assume?

Can a user own more than one object at a time?

What have you covered so far in class? We can't propose solutions that use techniques that your teacher hasn't covered yet.

-PatP

Each object can only be owned by one user at a time, I assume?
Yes.

Can a user own more than one object at a time?
Sure.

Propose any solution you have in mind, please. I will comprehend.

What identifies the exchange and what determines the sequence of exchanges? A date or a transaction number maybe? Can an exchange involve more than two items or more than two users? Are items unique?

I don't expect answers to these questions because I'd probably then want to follow them up with more questions anyway. What I'm trying to say is that good design requires proper analysis and understanding of the data and requirements. It isn't very feasible to do the analysis in an online forum. So take the following purely as an example or suggestion based on guesswork and assumptions rather than an accurate answer.

CREATE TABLE Exchanges
(TransactionNumber INT NOT NULL,
Buyer_UserId INT NOT NULL REFERENCES Users (UserId),
Seller_UserId INT NOT NULL REFERENCES Users (UserId),
ItemId INT NOT NULL REFERENCES Items (ItemId),
PRIMARY KEY (TransactionNumber, Buyer_UserId, ItemId /* ?? */));

I will answer the questions, just in case.

This is the hardest part. It doesn't matter what will determine the sequence, because these things are easily changed when it is wanted. I would love to concentrate not on small details, but on the big picture. Make any identification for a sequence you like.

Each exchange involves only item from each user. No, there can be no simultaneous exchanges among more than two users. Items are unique.

The fine folks here at DBForums take a rather dim view of doing people's homework for them. We'll help you with your homework, offer suggestions, etc. but we won't do it for you. I ban users for doing that.

What have you done so far?

-PatP

How do you know this is homework for a class?

Check the post counts...
Pat Phelan: 9,235
blindman: 8,937
We're not always right, but by this time we have a pretty good idea of what a homework assignment post smells like.

You can't determine this with mathematical certainty (i.e. a probability of 1), but experience will allow you to sense it with a high enough degree of certainty (say 0.9999 or better) that I'm willing to treat that as a certainty.

After you teach a few courses, you get a feeling for how intructors phrase assignments. The purpose of the assignment is to educate the person that solves it. The purpose of a real world problem is to accomplish some work, specific education is not required, just a solution to the problem. Homework is deliberately short on details, to focus attention on the material being taught. Real world problems are littered with detail, which require the solver to determine what is "wheat" and what is "chaff" for the problem.

The attitude of the person looking for help is also a good clue. Students wanting someone to do their homework are essentially thieves, stealing a solution from someone and stealing the learning associated with doing the assignment from themselves. People looking for a solution are usually "in over their heads" with a problem that is beyond their present abilities, but they are usually simply interested in getting a job accomplished and are usually eager to learn. Some experience on the internet allows you to sense this pretty accurately too.

-PatP

It's sad... it seems likely that someone in a db class is probably after a degree where they really will need to know it. I mean, we're talking about business majors and IT majors mostly, not so much English Lit. students.

And compared to the other courses these students are going to be required to take, db design is rather fun.