A phone chart for each customer or just one for all customers?

Question

Currently in college we learn like this:

It has a client entity, this client has its attributes and one of them is the phone, I want it to be possible to register several phones, we create a single table, in it will have the customer id and the phone, so we can register several phones with the same id customer’s.

Doesn’t it get heavier to search the phones of a single customer? It will be all together the phones of customers in a single table.

Do the companies do it this way? Or each customer will have their own table when a new one is registered?

Answer 1

If there was a single rule, I’d have everything ready and I wouldn’t need anyone else to do it. Companies don’t pay programmers to do what’s already done. Programmers are paid to create the best solution for that specific problem, its problem, which meets all requirements, and usually the developer should find these requirements, including those that even the user does not know. So it doesn’t matter what someone has done, or if many do it like this.

What is usually right is to have several phones in the customer’s own table. Simple like this, and fast. It is rare to see a reason for the phones to be registered in another table, and if you think you should do this you should find a good justification. Extra table always costs more expensive and should be avoided until it brings a compensating benefit. Your case has such a benefit?

But if separating I see no problem something to do as you think. If you see should justify. If doing right is not slow.

And if you’re saying that every customer should have a table just to register these phones, the answer is a definite no. If you have 1 million customers will you create 1 million tables just for phones? This makes no sense and will probably run into a technical problem as well as being unmanageable.

Answer 2

The scenario you are setting should be considered in relation to the DBMS (Database Manager System) which will use, more specifically, the DBMS model.

I will consider two models: Relational (as Postgresql) and Document-oriented (as Mongodb).

Relational Models

In relational models, the standard of good practice and modeling is based on Data normalisation, where one of the normal forms describes the need for the properties of entities (fields of a table), not to be multivariate.

In this way, to solve, using the scenario you mentioned, a client with multiple phones, the ideal is yes create a Client table and a Phone table where each customer will have n phones and each phone will belong to 1 customer, making the customer primary key relationship (customer id) to be added as foreign key field in the phone table.

Your doubt about performance, see, relational Dbms were created to handle high volumes of data and solve the relationships between tables as performatively as possible. Technically the simple solution is to create indexes for foreign key, which allows the engine to execute smart plans at times of queries.

So as an example we would have the following model:

Models Oriented to Documents

They are among a broader category defined as Non-relational Models or Nosql, specifically the Document-Oriented ones allow the modeling of data from an entire document as a record in a collection (name given to tables).

Mongodb for example stores a document in Bson format (Binary Json) and allows, in fact in most cases, guides, the "denormalization" of data, that is, in the specific case of multivariate fields, they are welcome.

See, like relational databases, these are also designed to handle high volumes of data, but in general, by minimizing the need to resolve relationships (joins) between multiple tables, the performance in many cases is higher because all the data is stored together.

For example for Customer and phones, the model could be with the Phones field storing an array:

Cliente {
   nome: "João",
   cpf: "00000000000",
   telefones: ["42-9999-9999", "41-0000-9999"]
}

Beware of the modeling

Finally, I agree with @Niero when he is emphatic about using a table or phone collection for each customer, this would be totally out of context because he would have to create tables for example: Phone_1, Phone_2, Phone_x, one for each customer, impractical from a technical point of view, and totally out of any good practice orientation and literature in both Relational and Nosql data modeling.