Results 1 to 2 of 2

Thread: what's the best relationship method for a user table with app tables

  1. #1
    Join Date
    Apr 2009
    Location
    Sydney, Australia
    Posts
    110
    Thanks
    15
    Thanked 1 Time in 1 Post

    Default what's the best relationship method for a user table with app tables

    I wanted to know your thoughts on information architecture for MySQL when it comes to a username and password table.

    The basic (and common) premise is that a user will need to enter a username and password to access their in-app details. I will need a "users" table for this with two fields "username" and "password" I assume. This is one table.

    A second table will deal with in-app objects the user owns (object-oriented tables).

    However, unlike in MS Access where you can create a LOOKUP field and therefore duplicate data and make connections, what is the best method to relate user data to user's app data?

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,694
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    create one table with the username and password and any other stats you can think of, such as date registered, ltime last logged in, infractions, email address, etc. In your other tables use a number that corresponds to the username, then use a JOIN to link the two tables. The benefits of this is that it reduces the chance that a user will be entered into the database with a different username in table 2, such as John "instead" of "john". Also it will make changing a person's username easier as all you have to do is change the username once in table 1 as opposed to 1000+ times in tables 2+. This is know as a Normalized table.
    To choose the lesser of two evils is still to choose evil. My personal site

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
  •