如何在mysql中建立一对多关系图,如何在数据库中建立一对多关系模型

I have a database containing tables using foreign keys to indicate relationships among them. In one table I have 2 columns..

1 is id (foreign key) and other contains friendsids (foreign key from friends table).

Either I can put the friends on separate rows:

| id | friendsids |

| 1 | 3 |

| 1 | 4 |

| 1 | 5 |

Or pack them into a single string field:

| id | friendsids |

| 1 | 3,4,5 |

In second way later I will separate friendsids using PHP explode(). Also do remember I have lots of records. Which way is more efficient and why?

Thanks in advance.

解决方案

Forget about splitting in PHP. What you are doing is a so-called mapping-table, and it really should be 1:1. That allows you to

easily INSERT IGNORE to add a mapping w/o checking if it already exists,

easily DELETE a mapping w/o checking if it already exists,

easily COUNT(*) the number of friends,

easily JOIN data for complex queries

search your data really fast with a UNIQUE-INDEX spanning both rows and a nonunique on the latter

save digits as digits instead of a string saving lots of ram and disk i/o

and propably many more.