0

This question is based on my plan at the thread.

The following figure shows relations in my database

alt text http://files.getdropbox.com/u/175564/relation-figure.png

I have two "help-tables": questions-subjects and check-moderator. I use the former because one question can have many subjects, while the later because more than one moderator can check a question.

I left out the 1-to-1 number out at the arrows.

The dotted arrow between the tables question and moderator-check indicates that there may be questions which moderators do not check.

This is my first database-project so there are mistakes in the tables.

What would you improve in the table?

Community
  • 1
  • 1
Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697

2 Answers2

4

User, UserInfo, Moderator and Password are redundant tables that offer no benefit.

They only express 1-1 relationships with User, so there is no need to normalize them into seperate tables:

Make one table:

  • UserId
  • Name
  • Email
  • PasswordMd5
  • IsModerator
FlySwat
  • 172,459
  • 74
  • 246
  • 311
  • I need to store the user's password in a hash -form such that nobody connot read it. **Do I need a table for it such that I can check effectively that the password given by the user matches the one in the database?** – Léo Léopold Hertz 준영 Jul 25 '09 at 19:29
  • 1
    No. You do the hash in your application. – FlySwat Jul 25 '09 at 19:31
  • 2
    To clarify, you never store the cleartext pass in your database. What good is having both clear and the hash in your DB? Store just the hash, and then apply your hash algo to the inputted password then just compare hashes. – FlySwat Jul 25 '09 at 19:35
  • @FlySwat: I know that the app works like, by multiplicating two very large primes, x and y, such that the product, z, is public: x*y=z. z is is the hash in the db and apparently y is in the db too. If user give the right password, the value of x, we let him in. -- **Should I have the parameter password-y in your suggested table too?** – Léo Léopold Hertz 준영 Jul 25 '09 at 19:39
  • Please, see my reply to your answer about how I understand your answer: http://stackoverflow.com/questions/1182798/to-improve-a-relation-figure-for-a-database/1182876#1182876 – Léo Léopold Hertz 준영 Jul 25 '09 at 19:43
  • 1
    Never invent your own hashing algorithm. Use SHA1. – FlySwat Jul 25 '09 at 19:50
  • @FlySwat: I am speaking of SHA1. I have never used it before. I only know its mathematical background. - **How can I use SHA1 algorithm in my database?** – Léo Léopold Hertz 준영 Jul 25 '09 at 19:55
  • Are you sure you're thinking about SHA and not RSA? Your answer above that refers to large primes and public products sounds a lot more like RSA than SHA. They are completely different algorithms with different purposes. – Greg Hewgill Jul 26 '09 at 00:41
0

Reply to FlySwat's answer

I changed my table to the following based on your answer.

alt text http://files.getdropbox.com/u/175564/table-problem-3.png

Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697