SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Q: User data stored in own database or in mixed tables?

    I am working on a simple web app that requires financial transactions to be stored and invoked and used for each user. My question is how thatdata should be stored. Should each user have their own database or would it be fine to have every single users' data stored in one table? Obviously different types of data will have separate tables. If they are stored together would it be fine if each row had an username entry in order to pull a users individual data?

  2. #2
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,220
    Mentioned
    237 Post(s)
    Tagged
    1 Thread(s)
    It really depends on what type of financial transactions we're talking about. It also depends on the amount of data you're going to store. Let's say that I'm a bank and I have a few thousands of customers. It also depends on the database. Logically, I would have a table for users with all the information related to them, and a table for transactions (pay-ins, withdrawls, etc) and a third table for bank accounts (bank account nbr, type of account, etc).

    The users table needs to be related at least to bank accounts (using user ID to create the relationship, more than the name), and bank accounts related to the transactions table. Then I could query the transactions of a paritcular user by querying the three tables. This is a logical structure.

    Now, would it be better to create a transaction table for each user? That would change the logic and for sure the whole structure of the database. It will be certainly harder to maintain yet searches for the transactions of a particular user would be faster as it would not be necessary to use a huge table for it, we will be using a table that was created only for this user.

    To know if it is worth it or not, we would need to benchmark and compare results. That would mean create the database structures, fill them with dummy data, and do the queries that we would normaly do and use the database optimization and measuring tools to know what's quicker.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    Now, would it be better to create a transaction table for each user? That would change the logic and for sure the whole structure of the database. It will be certainly harder to maintain yet searches for the transactions of a particular user would be faster as it would not be necessary to use a huge table for it, we will be using a table that was created only for this user.
    Having the correct indexes should take care of the performance issue.


Tags for this Thread

Bookmarks

Posting Permissions

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