Doubt in Data Normalization

Asked

Viewed 74 times

-3

I have a question regarding normalization. I have three tables: Vehicles, Filters and Combinations. It works like this, in the Combinations table I select the Vehicle ID in the Vehicle table and 4 filters related to each car in the Filters table . Ex.:

inserir a descrição da imagem aqui

Is the structure acceptable by the standards? or would it have to create 4 Filtrosn tables? This is linked in what I will call in PHP. For example:

$query = "SELECT * FROM Combinações c
             JOIN Veiculos v
             ON c.id = a.veiculo
                 JOIN Filtros f
                 ON..."       <<--- É aqui que estou em dúvida

How do I link the Filters table to the Combinations table ? and as I would call a 'echo' Oil Filter, Air Filter, Cabin Filter, Fuel Filter relating to the Filters table?

php mysql sql mysqli
  • 2

    It depends on what normal way you’re talking, on some it’s yes, on another it’s not, I think because normalization is not a magic rule that solves problems, you have a problem and everything needs to be normalized to meet its demand, without knowing the exact demand has no way of knowing whether it’s right or not. Unfortunately every time I write this the person repeats what he has already said and does not detail the demand and then it becomes difficult to say if it is right. I don’t know what those parts are for when they break down, so I don’t know if it’s right. The second part must have dup,have seen several times answers 3 tables

  • The demand you say is in terms of traffic? number of queries per time? I don’t understand...

  • I do not know if I will be repetitive, but I will try to explain how my system works: it is a system for mechanical workshop, so I have a list of vehicles and a list of specific parts of each vehicle (e.g.: Spin - Tyre X - Wheel X - Hubcap X - Screw X ; Pallet - Tyre Y - Wheel Y - Hubcap Y - Screw Y...). So when consulting a vehicle I will know which wheel, hubcap, screw, tire I use... (Note: the table image was an example I found ready, I don’t know if it is disturbing you)

  • 1

    How many pieces can you have in this? Always 4, can’t have 3 and can’t have 5?

  • Yes, always 4 pieces.

  • 1

    So the answer you accepted is wrong.

  • It’s...I just realized. I did but it gave a 'false-positive''...

  • I edited the question and put how it really works. Now I think it’s more understandable rsrs...note that in the table Filters has all filters, no matter your application (air, oil, ...) that I filter in php

  • 2

    The problem is that you now have two wrong answers to your question because it was not done properly from the beginning, and worse, it has positive in both.

  • I may have been wrong in the formulation of the initial question, but I do not see it as wrong answers, so much so that the initial structure I made was in line with that last answer. But I changed because it greatly increased the amount of information stored...

  • 1

    You just said they’re wrong even though they’re not.

  • Well...we can be discussing philosophically the meaning of the sentences...but did not say they were wrong, said it is not the best option, there are good and bad options, what do not classify them as wrong...follows the game...

Show 7 more comments

2 answers

1

I believe that the best would be:

table vehicles: remains identical

table pieces: remains identical

table Breakdowns: You need to build a relationship and the first questions are:

1) A vehicle may have more than one malfunction and the malfunction more than one vehicle? I believe that.

2) A breakdown may need to have more than one part and the same part be in various breakdowns? I believe so and this is a relationship N - N

Then you need to create an intermediate table for the relationship N to N (as both may contain more than one row of another table, as in a shopping cart).

Then you will have 4 tables:

  1. Vehicles
  2. Malfunctions
  3. Pieces
  4. breaks_pecas (id, veculo_id, break_id, peca_id) <-- the new that will bridge the relationship.

So you can join in several ways, for example:

// todas as peças que precisou em uma avaria
select pecas, from avarias_pecas ap, pecas p where ap.peca_id = p.id and avaria_id=X;

//todas as avarias de um veiculo
select veiculo from veiculos v, avarias a where v.id = a.veiculo_id where v.id=X;

Okay? I hope I helped

  • 1

    I understood your comment, I work with Magento and I’ve done some banks of virtual stores, in all I made a table for "pieces" and another for categories (as you mentioned the types of filters) a third table for filter_categories and other filter_vehicles; so you control and extract data like "filters for certain types of vehicles", "all vehicles that use that filter" and even display it in more than one situation if it’s a more generic product. Good luck with your bank!

1


To meet what you need, correctly, you must change the breakdown table to a "vertical growth structure":

Relacionamento 1->N

Because, if each piece table record turns a column in the breakdown table, every time you create a new record you will have to run "alter table" in the breakdown table.

Then your consultation would be:

select * from Avarias a
 join Veiculos v on v.idveiculo=a.idveiculo
 join Pecas p on p.idpeca=a.idpeca

  • I changed the structure! I’ve tried that too, but it didn’t work...

  • The problem with doing so is that there would be many lines for each vehicle. But if there is no other way...

  • 1

    @Nosredna, in my answer, the "dirty" table is just the N-N faults_pieces; and the other 3 would be more practical for control. Are different ways of doing

  • @Leonardonegrão I understood... I had done another way too, type: table Combinations, table Vehicle, table Air Filter, table Oil Filter, table Cabin Filter, table Fuel Filter - here I listed each table 'Filters' and table 'Vehicle' in table 'Combinations'

  • 1

    @Nosredna you asked which correct way to do it. The correct way is like this. Now, if on the other hand you want to "show in some other way", use the programming language (php,perl,.net,java) to mount an html or in the form as you want. But the standard database for this case is like this.

Browser other questions tagged php mysql sql mysqli

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