-1

I am trying to create a server for POP3 protocol. I'm relatively new to mySql and would like to create a simple database for storing users (username and password) and their corresponding emails(some text, not email-ID).

Example: let's say user1 has username1 and password1. I would like to create another table which points to the user1 and contains his emails there.

My main purpose is to run a query using Java and access users (using their username and password) and their emails.

How can I do this?

Piyush N Ladani
  • 1
  • 1
  • Do you know something about `JDBC`? Look there: http://www.tutorialspoint.com/jdbc/ – Michał Piątkowski Feb 16 '17 at 19:19
  • yes i am aware of jdbc but could not able to figure out how to create a table1 (containing emails) under the table2 (containing users) pointing to table1 – Piyush N Ladani Feb 16 '17 at 19:23
  • I'm not following your question. Do you mean that you already have a table that contains user1, username1, password1 and you would like to create another table that has all of user1's emails? – David Feb 20 '17 at 17:41

1 Answers1

0

Take a look at this answer to know how to store passwords in database.

You can have your design something like this:

users
    - id  int PK
    - username varchar unique not null
    - passwordhash varchar not null

user_emails
    - id int PK
    - user_id int not null FK -> users(id)
    - email varchar not null

I am not going to write down DDL for you. Check out vendor documentation for create table syntax.

Community
  • 1
  • 1
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • 1
    thanks for your valuable response. even i was thinking the same as you just outlined but the problem is , i need to maintain the numbering of all the emails i.e. all emails cant reside into one single table. each user need to have his own table for emails. i hope you will get my point. – Piyush N Ladani Feb 16 '17 at 19:28
  • 1
    I didnt understand. Why on earth would you create table a new table for each user? You can get the numbering from a single table while doing a select. See this - http://www.mysqltutorial.org/mysql-row_number/ – Gurwinder Singh Feb 16 '17 at 19:39
  • is it possible to assign a foreign key to an entire table rather than its individual rows ? – Piyush N Ladani Feb 16 '17 at 20:39