CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Cannon Palms
Cannon Palms

Reputation: 126

How to design a database in which there are many users and each has a list of multiple contacts?

So far all I have configured is a table for users where each user has a unique user_id. However, I need to store a list of contacts for each user. This would only need to contain the user_id of each contact. However, I'm faced with a design challenge.

Should I create a table for each user in which to store that users list of contacts? Is this a scalable solution?

Or should I create one table with two columns, user_id and contact_id, looking something like this:

------------------------------------
| user_id (INT) | contact_id (INT) |
------------------------------------
|    10001      |       9945       |
|    10001      |       2239       |
|    10002      |       9636       |
------------------------------------

I'm afraid that if I went with the second option, the lack of unique indexing and the sheer size of the table would eventually make even SELECT * FROM contacts WHERE user_id=10001; because every entry would need to be iterated over each time.

What is the best way to organize these data?

Upvotes: 2

Views: 353

Answers (3)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385295

The single normalised table is absolutely the correct approach.

You're worried about its performance, due to a concern about "lack of indexing".

Lack of indexing? Why lack of indexing?

Make your primary key (user_id,contact_id) — which makes sense semantically an' all — and that's all you need.

Never, ever have a variable number of tables. "A table for each user" is when you get booted from my team. ;)

Upvotes: 3

AaronLS
AaronLS

Reputation: 38394

I'm afraid that if I went with the second option, the lack of unique indexing and the sheer size of the table would eventually make even SELECT * FROM contacts WHERE user_id=10001; because every entry would need to be iterated over each time.

An index on user_id will eliminate this problem. Rather than iterating, it will use search algorithms that perform well even over large numbers of records. A clustered index, if where user_id= is the most common type of query, will make performance even better.

The first option, a table per user, is absolutely the worst way you could ever do it.

Upvotes: 0

amaster
amaster

Reputation: 2163

The second way with the "pivot" table is the best way and best practice for normalization. You would also want to make foreign key relations with indexes to the proper tables and columns.

Upvotes: 1

Related Questions