Save column array or multiple records?

Asked

Viewed 32 times

1

I am in doubt of which is the best option in the performance/performance when reading the data. Aiming for one with many records and being accessed all the time.

I need to specify which neighborhoods the client serves, ie a client for multiple neighborhood Ids, it is better to do...

Examples of Bank Structure

Client | Neighborhoods

1005 | 1,2,3,4,5,6,7,8,9,10,11,12 (Colunaarray)

OR

1005 | 1

1005 | 2

1005 | 3

...

php mysql

  • Are these whole values for neighborhoods ids? If so, it seems that it is a many-to-many relationship, having three tables: customers, neighborhoods and the relationship table. Worry about performance at this level only if you identify a bottleneck in your application. Otherwise, do what makes the most sense;

  • I would opt for the second in all cases, I find it hard to the first one worth just on some occasion. You may want to do a neighborhood search. For example, which customer meets neighborhood "3", this would be extremely slow in the first case, we would have to use a LIKE and not take advantage of any index. Already in the second one `"WHERE Neighborhoods = 3" resolves.

1 answer

1


In the first approach you are creating multi-valued columns:

Cliente | Bairros

1005 | 1,2,3,4,5,6,7,8,9,10,11,12 (ColunaArray)

What’s the problem with this approach? Roughly speaking, every time you need to pick up all customers from a particular neighborhood you will have to make a like, i.e., a generic search in the column neighborhoods, this can be much worse than having "multiple relationships," I mean in terms of performance and data maintenance (manipulation).

I found a post very interesting as to columns multivaloradas in databases: http://rberaldo.com.br/usando-campos-multivalorados/

In the second approach, using a table for customers, one for neighborhoods and a third to keep the relationship going many-to-many you remove this problem concerning multivariate columns. Advantages? You will have normalized tables (First Normal Form or 1FN), in addition to simplifying the process of seeking clients by neighborhoods.

First normal form (1FN) - This is considered a part of the definition of relationship in the basic relational model. Its definition provides that all attributes of a relation must have their defined values on atomic or indivisible domains. In other words, the fields of a table should not be composed or multivariate. See below a figure demonstrating the normalization of a table that is not in the 1FN for two relations.

For more information on normal forms: http://www.itnerante.com.br/profiles/blogs/desmistificando-as-formas-normais

It is important to keep in mind that there may be special cases where multivariate columns are needed, however whenever possible it would be recommended to avoid using this approach.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.