user1126245 user1126245 - 6 months ago 19
SQL Question

Nested table in mysql database

I have created a database like this

+----------------+------------------+
| name | password |
+----------------+------------------+
| john | ******** |
+----------------+------------------+
| james | &*^^%^ |
+----------------+------------------+


Is there any way to create a new table row by column friend,messages, and message from for john and james and other those who join my site?

Answer

You could add those columns to your table, but putting that kind of information in the user table is a bad idea.

You should create new tables and use a JOIN to fetch the data:

First, add an ID column to your existing table.

|----------------|------------------|------------------|
|   ID           | user             | password         | 
|----------------|------------------|------------------|

Then, create a new table with the wanted columns:

Table friends:

  • ID
  • USER_ID_FRIEND
  • USER_ID_FRIENDEE

Table messages

  • ID
  • USER_ID_FROM
  • USER_ID_TO
  • DATE_SENT
  • READ
  • MESSAGE_TXT