Best practices with large amount of data I'm building a solution and I have a question.

All my data is currently stored in a unique table. Each row of data is associated with a type.

My question is, what's the best practice for defining a table structure: all data in an unique table with associated type or data stored in dedicated type table?

eg: how to store people and associated colors.


all data in an unique table like id, people_id, color_id
data stored in table dedicated to color:

table blue: id, people_id
table red : id, people_id



In my opinion, it's better to have everything in a single table to avoid complex queries, but I'm scared about query time with billions of rows.
There are 3 common contemporary database designs:

1) Most database design is based on normalization, so have a look at that.

2) When that has performance problems denormalization is a common route. 

3) When vast amounts of data are projected database design has moved away from these techniques (1 and 2 are two sides of the same coin) and new technologies have been used, for example have a look at noSQL.

Unless you are dealing with seriously vast amounts of data you'll probably want to start with 1) and possibly look at 2) if and when you have performance issues. 

"Everything in a single table" is generally a bad idea as that table becomes huge, with a great deal of contention on it - every part of the application using it constantly. This would lead to deadlocks, for example, making queries slow.