Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    Toronto
    Posts
    13

    critique this design model

    I'm building my first db and would like some feedback on my model. I'm going to build a site for users to evaluate other user's portfolios (containg images), in which some (but not all) users may be a part of one or more teams. I have ommitted many of the rows of the tables, but have retained the keys and relations.

    Legend:

    Table Name
    Key
    Code:
                        |-------------------------------------------
       ------------     |    --------------         -------------  |
       |  User    |     |    | Membership |         |  Team     |  |
       ------------     |    --------------         -------------  |
    |->|  handle  |<----     |  tname     |<------- |  tname    |  |
    |  |  lname   |     ---- |  handle    |         |  admin    |---
    |  |  phone   |     |    --------------         -------------
    |  ------------     |
    |                   ---
    |  -------------       |   ---------------
    |  | Portfolio |       |   |  Evaluation |
    |  -------------       |   ---------------
    |--|  handle   |<----  ----|  critic     | 
       |  last_mod |     ------|  subject    |
       -------------           |  rating     |
                               ---------------
    Relations

    Team.tname ------------->Membership.tname
    Team.admin ------------->User.handle
    Membership.handle ---->User.handle
    Evaluation.critic --------->User.handle
    Evaluation.subject ----->Portfolio.handle
    Portfolio.handle -------->User.handle

    Questions

    Is this a sound model? I know there are no many-many relations, but I am unsure about the circular nature of the tables

    Should I use a user_id serial instead of a handle as the user primary key?

    Is it a good idea to store user passwords or hashes in the user table?

    *Any* help is appreciated, thanks.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    It looks good to me. It isn't circular since the user table does not rely on any of the other tables, so you can't get in an infinite loop.

    I store password hashes (one way encryption) in the database and then compare the user-entered encrypted password to the one in the database.

    Serial (I assume it would be an integer which counts up?) does have slight performance benefits in that it is smaller (more rows fit on a database page, more rows can fit in memory, etc.) and compares faster than character data. Also if the user decides to change his or her handle you must now cascade that through to every table which references it, and there may not be indexes on those columns in the ref tables (meaning it will table scan all of the other tables). So you now have to place indexes on handle (although in this case it looks like you will anyway) and again character data is larger and slightly slower than numeric.
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2002
    Location
    Toronto
    Posts
    13
    thanks for the reply.

    i had thought using a numeric data type would improve the database performance, but hadn't considered the possibilty of a user changing their username. i think i'll use the serial data type.

    i'd like to know a bit more about the best way to store passwords. you mention hashes, does the encryption occur on the client or server side? how do you authenticate with that type of system?

    in your opinion, for my database, should i use postgreSQL or mySQL? or perhaps something else altogether?

    thanks.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Encryption can be done either inside the database (with triggers or stored procedures) or in the application.

    I prefer to use the application.

    Basically you run the plaintext password through a hash function which turns the plaintext into a unique string. The MD5 function guarantees that for any given input the output will always be unique.

    So, you do something like this in PHP (or whatever language):

    $password = "myVoiceIsMyPassword";

    $hashed_password = MD5( $password ); // $hashed_password is 32 characters long and
    // un crackable. It looks something like this:
    // Dfh3dkj4akgG54skldjL5k678

    SELECT 1
    FROM user
    WHERE username = '$username'
    AND password = '$hashed_password'


    If 1, then they match, else failure.
    Thanks,

    Matt

Posting Permissions

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