douhong9210
douhong9210
2011-03-01 02:58

帮助我为iou Web应用程序设计数据库模式

I am thinking of making an iou (i owe you) web app where I can keep track of who owes me and what. I am doing this as a side project. Anyways, I have no experience with database schema design. The app design is shown in the img below: enter image description here

Right now, I know that I need to have a user table with at least the following:

UserTable:
username, usernameId (pk), password, passwordHint, email

Then, I get confused on how to proceed from there. Should I make a new table for every single Person#1, Person#2, etc? Or, should I have another table IouTable with all the "transactions" and use the username as the fk?

IouTable:
usernameId, personName, amount, description, date

So, when, user Yko logs on, it will run this query: SELECT * FROM IouTable WHERE usernameId = username (may have gotten it wrong here)

Then, I would take the rows and separate out each person individually and have each transaction for each person on its own page.

Thanks in advance for any help/advice/suggestions!!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • duanshan1511 duanshan1511 10年前

    Definitely one table for all IOUs.

    Scenario 1: As Yko I want a list of all the persons I owe to and how much in total

    SELECT personName, sum(amount) totalOwed FROM IouTable WHERE usernameId = <Yko>
    

    Scenario 2: As Yko I want to know how much I owe Bob and why.

    SELECT amount, description, date FROM IouTable WHERE usernameId = <Yko> and personName = 'Bob'
    

    Let's look a little closer to your case.

    1. The User table looks quite good. The key candidates include username, usernameId and email. The surrogate usernameId is as good as any for the key.

    2. The IouTable lists all the IOUs. There are a couple of issues with this table.

      • Perhaphs a Yko has loaned exactly €2 from Bob twice the same day and doesn't bother to fill the description. In this case, you don't have a proper way to distinguish the two transactions. You probably should add a transactionId to uniquely identify the transactions

      • Furthermore, Yko might have two friends called Bob. How can Yko separate these two from each other. Should there be a list of friends Yko can loan from?

      • Eventually Yko should pay up. Yko can probably clear out the debt from the table, but then how can we find out later on who has loaned how much to whom? Should there be a column that tells if the dept has been paid?

      • Some friends of Yko might even consider being paid back in increments. Yko might edit the original amount owed but then again we wouldn't know much was needed in the first place. Should there be a column that tells how much of the dept has been paid so far? If Yko want's to monitor the debt more closely, a list of all repayments might be good idea. Then you could count the total amount of debt left by summing up how much has been repaid.

    点赞 3 评论 复制链接分享
  • douhan6738 douhan6738 10年前

    My company started our own little side project with the same intentions a couple years ago, and we finally released it last week. It grew into much more than was originally intended, and can now track IOUs of any kind and integrates with Facebook and Twitter. If you want to check it out, go to IOUmate.com

    I'm certainly not trying to dissuade you from building your own app. However, if you're looking for this functionality (like we were) and just couldn't find a solution, check out our app and perhaps save yourself some time.

    Thanks, Dan


    P.S. I would recommend that you add a table of friends to normalize your IouTable. Each IOU can then be associated with a user and a friend. If you want to go in the direction we did, friends could either become members or not. Good luck!

    点赞 评论 复制链接分享
  • duanjiao1256 duanjiao1256 10年前

    If you mean it's to track IOUs between 2 users, then the IOU table would have a foreign key back to the Users table, one for the "ower" and the other for the "owed".

    点赞 评论 复制链接分享
  • dongyupan4850 dongyupan4850 10年前
    TABLE User:
    username, password, passwordHint, email
    PK = username
    
    TABLE Iou:
    username, pesronName, date+time, amount, description, repaid(true if personName has repaid it yet)
    PK = username,personName,date+time (or, make an auto-incrementing id num)
    
    点赞 5 评论 复制链接分享

为你推荐