CODEWITHSUNDEEP
sqldatabase-design
Arvie
Arvie

Reputation: 147

How to model one-to-many relationship in database

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

Answers (3)

CaveCoder
CaveCoder

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

Zsolt Szilagyi
Zsolt Szilagyi

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

  • 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.

Upvotes: 2

christopher
christopher

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

Related Questions