Reputation: 147
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.
Upvotes: 1
Views: 1298
Reputation: 791
As said before option one is the way to go, don't wory about performance, option 1 has been working efficiently in so many databases for so many years..
Just remember do place the right indexes on the right tables, and you are good to go.
SQL is made to handle big amounts of data
Upvotes: 0
Reputation: 5016
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
and propably many more.
Upvotes: 2
Reputation: 27356
The rules of database normalization dictates that you only have 1
value in 1
field. This is so you can access any single value with the correct primary key, rather than have to do some ugly code to split up the string returned.
Whenever you're designing a database, you should always keep the normalization rules in mind. Here is a link to the wikipedia article.
And in case you forget:
The Key, the whole key and nothing but the key - so help me Codd.
Upvotes: 3