Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Suggestions on a database design needed

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What have you covered so far in class? We can't propose solutions that use techniques that your teacher hasn't covered yet.

    -PatP

  4. #4
    Join Date
    Sep 2008
    Posts
    4
    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.

  5. #5
    Join Date
    Sep 2008
    Posts
    4
    Quote Originally Posted by Pat Phelan
    What have you covered so far in class? We can't propose solutions that use techniques that your teacher hasn't covered yet.

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

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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 /* ?? */));

  7. #7
    Join Date
    Sep 2008
    Posts
    4
    I will answer the questions, just in case.

    Quote Originally Posted by dportas
    What identifies the exchange and what determines the sequence of exchanges? A date or a transaction number maybe?
    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.

    Can an exchange involve more than two items or more than two users? Are items unique?
    Each exchange involves only item from each user. No, there can be no simultaneous exchanges among more than two users. Items are unique.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  9. #9
    Join Date
    Oct 2008
    Posts
    8
    Quote Originally Posted by Pat Phelan
    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?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by aetherealize
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by aetherealize
    How do you know this is homework for a class?
    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

  12. #12
    Join Date
    Oct 2008
    Posts
    8
    Quote Originally Posted by Pat Phelan
    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.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •