Difference between relational table and online record

Asked

Viewed 254 times

2

I have a structure of companies and categories organized in 3 tables:

  • empresa -> Registration of all registered companies.
  • categoria -> Registration of all registered categories.
  • rel_categoria-> Relationship between empresa and categorias.

Where I store the data as follows in the table rel_categoria:

id (primary_key) | id_empresa | id_categoria

And when I need to get the categories of a company, I make the related selection. So far everything ok.

But I came up with the following question: Why use an extra table to save these records and I can store them in a column in the table empresa?

Example in the table empresa:

id | nome_empresa |  categorias  | [..etc..]
 1 | 'Lojinha'    | 1, 7, 14, 16 | ...

The only reason that comes to mind would be in case of a count. Ex.: How many companies use the category X?

Is there any other reason? Any structural questions? Performance? etc.. I manage the bank through the MySql and PHP.

mysql database modeling relational-model

  • I may be talking nonsense but in ORM frameworks like Hibernate for Java and Eloquent for PHP, they use this pattern. I believe it is easier to return for example the category(object) of a company.

  • @Raphael when you say 'this pattern', refers to the related table pattern?

2 answers

6


The structure you set up, with tables companies and categories and a third relationship table follows the concepts used in relational database.

Then we can start with questions like modeling, normalization, normal forms, etc....

On the assumption that an ENTERPRISE may have several CATEGORIES, and a CATEGORY may be present in several UNDERTAKINGS, we have a relationship N:N which is represented on the bench using this relationship table.

That too impact on performance. For if you assemble the normalized structure, with primary and foreign keys, indexes and the like, the consultations are optimized.

Imagine if you store all the categories of a company in one varchar column, to query performance will fall, because you will need to use functions for string handling for the location of the desired information, among other things.

  • In fact I do treatment of strings breaking in commas, mounting arrays, etc.. It’s a kind of structure that I’ve seen a lot but with little "why" to do so. But so clearer, thank you.

  • Actually, sometimes we do things and how it works and we don’t worry about the theory, but we need to understand the concepts and the why of things. So we can make better decisions when implementing our solutions.

2

Have you answered.

Although you can also count or other search types if you do a column with her categories. Whether you compensate depends a lot on what you want to do. You can almost always do well with the multi-valued column. It is more complicated to access the data of the categories in this format. Nothing exaggerated, but someone can look at your query and not understand what it does right away. It’s more likely to make a mistake.

You only need an extra table even when accessing categories with no link to the company. It’s rare to need this. But in other situations this may not be so true. You can even do it without the extra table, but it can be very slow to have to look at all companies to find out what you want, but I doubt that this will run in most situations, especially in this specific case reported.

On the other hand creating an extra table will almost always require a JOIN which is usually an expensive operation and it is good to avoid if you can do it decently.

If you have some software that requires one way or another, then you have to follow what they say, or try to abandon that requirement.

Normally I would go column without extra table, at least until I have an indication that this would be bad, but I don’t know your specific case.

People preach a lot to normalization without looking at the real case. It is one thing to normalize what needs to be normalized, what makes sense for that model, another is to force normalization where it does not need just because one has learned that normalizing is always good.

Browser other questions tagged mysql database modeling relational-model

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