PDA

View Full Version : critique this design model

catch
03-19-02, 21:27
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

|-------------------------------------------
------------ | -------------- ------------- |
| 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.
MattR
05-23-02, 13:32
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.
catch
05-25-02, 19:25
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.
MattR
05-28-02, 16:58
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.