Basic structuring of MySQL database

Question

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 MySQL for demonstration.

Is there any better way?

I was afraid someone would say that. :P Thanks for the input. — delaccount992
– delaccount992, Commented Feb 20, 2011 at 4:51
friends_lists is usually called an "association table" and is the standard SQL approach for this sort of thing. — mu is too short
– mu is too short, Commented Feb 20, 2011 at 4:57

MestreLion · Accepted Answer · 2011-02-20 05:17:03Z

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)
}

delaccount992 · Accepted Answer · 2011-02-20 05:01:05Z

1

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.

edited Feb 20, 2011 at 5:01

delaccount992

1,3333 gold badges15 silver badges21 bronze badges

answered Feb 20, 2011 at 4:51

Can't Tell

1941 silver badge5 bronze badges

6 Comments

nathan gonzalez Over a year ago

i'm pretty sure that's exactly what he has.

Can't Tell Over a year ago

Ah.. yes. Seems like I misunderstood. Sorry about that.

Mike Sherrill 'Cat Recall' Over a year ago

@nathan gonzalez: It's not exactly what the OP has. Can't Tell has wisely omitted an unnecessary auto-increment from the table person_friend.

nathan gonzalez Over a year ago

@catcall, i don't know that i would call it wise, as the lack of a surrogate key in this kind of table makes it difficult to locate and delete or update the row you're looking for. seems a lot easier to say delete the row with an id of x than delete the row that has a parent of my userid and a child of this other user id, not to mention that indexing would be more efficient. why make something more complicated than it needs to be, right?

Mike Sherrill 'Cat Recall' Over a year ago

@nathan gonzalez: You already have two foreign keys to the parent table. That pair of columns has to be declared either PRIMARY KEY or UNIQUE. Both those columns will be indexed. Any time you need to delete a single row from person_friend, you're already going to know the id numbers of both the person and the friend. (If you don't know both those id numbers, you have no business deleting rows.) All that additional auto-increment id number does is degrade performance. (And increase the probability that you'll delete the wrong row.)

|

Collectives™ on Stack Overflow

Basic structuring of MySQL database

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related