Hello. How to connect the left table with a group of 3 tables on the right? Image1 Only now it turns out to connect the method presented in the 2nd image

Image2 For example, a user selects a specific make, model, and generation of a car, and his choice (make, model, generation) needs to be written to another table.

create table CarBrend( [BrendID] int identity (0,1) not null, [Title] nvarchar(max) not null, constraint PK_BrendID primary key ([BrendID]) ) create table CarModel( [ModelID] int identity (0,1) not null, [BrendID] int not null foreign key references CarBrend([BrendID]), [Title] nvarchar(max) not null, constraint PK_ModelID primary key ([ModelID]) ) create table CarGeneration( [GenerationID] int identity (0,1) not null, [ModelID] int not null foreign key references CarModel([ModelID]), [Title] nvarchar(max) not null, [StartProduction] date not null, [EndProduction] date not null, constraint PK_GenerationID primary key ([GenerationID]) ) create table Car( [CarID] int identity (0,1) not null, [BrendID] int not null foreign key references CarBrend([BrendID]), [ModelID] int not null foreign key references CarModel([ModelID]), [GenerationID] int not null foreign key references CarGeneration([GenerationID]), constraint PK_CarID primary key ([CarID]), )

I doubt the correctness of the 4th table (Car). Tell me what to do in this case.

The first CarBrend table contains the Nissan car brand (and other brands). The second CarModel table contains the Nissan Primera model (and other models). The third CarGeneration table contains all generations of Nissan Primers - P10, P11-120, P11-140, P12 (and others). And in the Car (4) table, entries from all 3 previous tables are combined (to form a complete car).

    5 answers 5

    The DB should not allow to enter in itself the inconsistent data. This is a basic design principle. The second method you propose, that is, with three fields at the car, allows you to create one car of the Mercedes brand and a specific Mercedes W140 model and another car, the same model as the Mercedes W140, but to supply it with the Zhiguli brand. So this database itself is asking for the introduction of incorrect data into it.

    The second design principle is the simplicity of changing one entity. Suppose we were wrong and attributed the W140 model to the Lada brand. created several such machines, realized it and decided to fix the brand. In the first case, we only need to change the brand for a particular model. And in the second one, go through all the cars and change the brand with them.

    If you look at the design theory, the second option, with three links, breaks the third normal form .

    Consequently, the machine should have only a link to the revision of the model and nothing else. this will allow both to avoid the introduction of inconsistent data and provide the principle of changing one entity in one table. So your first picture is correct.

    • I did not understand the second paragraph, what 2 cases do you mean? I supplemented my question with an example with a specific machine and related tables. - AlexGhosTTT
    • one
      @AlexGhosTTT Two cases are your two pictures, which is at the top of the question. As for the user who made the choice, he chose a specific generation as a result and it’s enough to save the generation id, because using it you can perfectly get both the model and the brand - Mike

    Generally speaking, your Car table violates the third normal form — because it has a non-key dependency, GenerationID -> ModelID -> BrendID . From the point of view of the theory of relational databases, in this table one attribute of GenerationID sufficient, and ModelID and BrendID redundant.

    This could lead to a so-called. insertion, update and delete anomalies (terms for googling), do not have CarModel and CarGeneration . But in the current form, not everything is so good: your database is not immune from incorrect data. A minor error in the code, and you will have a Lada model of a Mercedes W140 (thanks to Mike for the example).

    However, blindly clearing these fields is also wrong. They do an important thing here - speed up the search. Suppose that these fields are removed from the Car table, and we need to find all the cars of a given brand. In this case, the query will be executed like this:

    1. Getting all models of a given brand
    2. Getting all generations for each model
    3. Getting all the machines of each generation

    Two nested loops for such a simple query! And in no way can it be accelerated by indexes - because indexes cannot be built on several tables ...

    Now how to do it.

    Option 1 - use composite foreign keys

    This method allows you to add data validation, making the insertion of "strange" cars impossible. This is done like this:

     create table Car( CarID int identity (0,1) not null primary key, BrendID int not null, ModelID int not null, GenerationID int not null, foreign key (BrendID) references CarBrend(BrendID), foreign key (ModelID, BrendID) references CarModel(ModelID, BrendID), foreign key (GenerationID, ModelID) references CarGeneration(GenerationID, ModelID), )

    Do not forget to add indexes, because without them, this complication of the structure does not make sense.

    Option 2 - using indexed views (for MS SQL only)

    You can remove these attributes from the source table, but create an indexed view that will speed up the search:

     create view CarView with schemabinding as select Car.CarID, CarModel.BrendID, CarGeneration.ModelID, Car.GenerationID from Car inner join CarGeneration on Car.GenerationID = CarGeneration.GenerationID inner join CarModelon CarGeneration.ModelID = CarModel.ModelID go create unique nonclustered index PK_CarView on CarView(CarID); create index IX_CarView_BrendID on CarView(BrendID); create index IX_CarView_ModelID on CarView(ModelID); create index IX_CarView_GenerationID on CarView(GenerationID);

    In the Enterprise Edition of MS SQL Server, the very presence of this view will speed up the search for cars by brand and model (in fact, in this edition, the indexed view allows you to create those same indices on several tables). In the minor revisions, however, an explicit request for a view with the key NOEXPAND is required:

     select * from CarView WITH(NOEXPAND) where BrendID=5

    If the generation of such requests is impossible due to the limitations of the ORM, this can be bypassed with one more representation:

     create view CarView2 as select * from CarView WITH(NOEXPAND);
    • Unfortunately, I don't know indexes very well (I may not even know it at all), so I will try to focus on Mike's answer. Thank you for the answer. - AlexGhosTTT
    • @AlexGhosTTT and you have to find out about them anyway. Otherwise, the base will hopelessly slow down after accumulating a certain critical number of records. - Pavel Mayorov

    It seems to me that you connect everything correctly, all connections seem to be correctly established.

    • @DenisBubnov on the contrary, this is a complete answer to the question. Just wrong, but this is not a reason to convert it into a comment. - Pavel Mayorov
    • @DenisBubnov what are the details when he says "you have it all right"? - Pavel Mayorov
    • @DenisBubnov and what? Understand, you scare away newbies with sample comments that are inappropriate for the situation. He did not write a comment, he replied . And his answer deserves cons - but no comment "is not the answer to the question . " - Pavel Mayorov

    It seems to me that the connections between the right three tables are redundant, the left table is enough, it links the three right ones. Yes, and in the left one you can combine 3 foreign keys into one primary one, although it may be that it is better not to do it, more precisely it depends on the requirements of uniqueness.

      Your architecture is complicated somewhere on the level of flights into space, you have so many tables, and here, in fact, 2 tables will suffice, car_br a nd and car_model, everything else is redundant duplication . What kind of load the Car table carries is generally incomprehensible; it is like the CarModel table + the GenerationId field.

      1. In your case, the model necessarily has a production date and a production end date, why is it in a separate table? This field is related to the record itself and must be inserted into the car_model table.
      2. I recommend using the most commonly used case table names snake case.
      3. It is advisable to rename the field br a ndId, modelId simply to id to eliminate unnecessary duplication in the column names, although this is of course a matter of taste.

      Use one key : car_model.brand_id = car_brand.id

      • 1. If the revision of the model is different than that - then the third table is definitely needed. 2. I, for example, usually use the same small letters, but where it is written that it is “correct,” why not use word highlighting. 3. In no case, you do not need to rename the fields, and then later in the request for 10 id fields and it is easy to get confused, but with the same id field names containing the essence of what id you can write natural join. - Mike
      • @Mike 1.A in this case is not needed :) 2 fields, and then mandatory. 2. I probably got rid of the word correctly, it's just possible to call it the most used case. 3. Here I do not agree at all .. why duplicate the name of the table in the field, when its meaning is obvious and there is no indication of another table? Is it convenient for you to constantly write SELECT * FROM user_attributes WHERE id_user_attribute ..? By your example, you can say that the fields should be address_user_attribute, passport_number_user_attritute. If you do not want to get confused, then you need to use the 'table name. Field', and when displaying 'as' - Firepro
      • Well, personally, I would call this field usatid and it is so convenient for me to write;) So this is a matter of taste of the person who writes. And I do not think it is right to impose my taste on others clearly in response (And the word must be in 3n, as it alludes to this). - Mike
      • @Mike A matter of taste is yes :) but I tried to show the practice of what to write too much if one part of the name already displays the whole essence of the column in conjunction with the name of the table :) Corrected the answer in accordance with the comments. - Firepro