Basic structuring of MySQL database

I'm fairly new to MySQL and I need help with a relatively basic question.

Say I have an auto-increment table that lists individual people by row. I include all of the basic information about each person such as name, age, race, etc in the columns. But say I want to include lists of the people's friends as well. Since these lists would be dynamic and to my knowledge you cannot have two auto-increment variables in a single table, it would not be possible to include the friends lists in that specific table as there are no such things as sub-tables or anything of the sort in MySQL (again to the best of my knowledge). If you wanted dynamic friends lists you would have to make a new table solely dedicated to that purpose.

Am I right in this thinking? Or am I missing something?

Here is my current general idea (which I rather dislike):

table people_list {
  person_id (auto-increment)
  name
  age
  race
  ...
}

table friends_lists {
  friendship_id (auto-increment)
  person_id1
  person_id2
}

Note that I just made up the syntax in essence of MyS开发者_运维百科QL for demonstration.

Is there any better way?

Your approacch is correct... theres no other way to do this other than an auxiliary table (friends_lists in your scenario). Thats how one achieve a "many-to-many" relationship between two tables.

In your case, the two tables are the same (people_list), but, conceptually, they can be thought as "friends" and "people"

But, may i give you a few hints about this approach?

1 - Every table is, in a way, a "list". So, why the suffix "_list" ? Dont, for the same reason we dont use plural for table names (its product, not product*s*. Of course where will be many ;)

2 - Instead of using an auto-increment id at friend, turn both person_id1 and person_id2 into the primary key. You get rid of a useless column, AND this way you enforce that each pair Person X - Friend Y is unique.

3 - Give person_id1 and 2 meaningful names, based on context, like "person_id" and "friend_id"

To sum it up:

table person {
  person_id (auto-increment, primary key)
  name
  age
  race
  ...
}

table friend {
  person_id (foreing key from person, primary key)
  friend_id (foreing key from person, primary key)
}

Why not

table people_list {
  person_id (auto-increment)<
  name
  age
  race
  ...
}

table person_friend {
  person_id(of person)
  person_id(of friend)
}

Take a look at this to understand better about one to many relationships.