The resulting data scheme

Duplication attacked, apparently due to incorrect design.

Comments to the scheme:
- services (Service) are bound to an order (Order) using a table (Order_Service)
- each order service (Order_Service) is performed by an employee, connection: table (Employee_Order_Service)
- services are of two types:
+ translation (TranslationInfo), stores the name of the text that is translated, the languages ​​from and to which it is translated (LangFrom, LangTo)
+ certification of translation (NotarialInfo), also stores the name of the text, languages, the number of copies of the certification.

But there is such a flaw ... Such a problem, look, the type of the service "translation" and "certification" contain many identical fields , namely: the file name, the language "from", the language "to". Therefore, when changing, for example, the name of the text, you will need to change it in both TranslationInfo and NotarialInfo. You can only verify the texts that were translated , so it would be better to place the "CopyCount" field in the "TranslationInfo" table, and not to create the "Notarial" table at all! But the assurance must have its own performer, its price. The translation also has its performer and price, respectively.
How to be? How to fix?

  • why don't you combine the TranslationInfo and NotarialInfo tables into one with a typeId column - Konst
  • With this approach, is it worth putting the "different fields" in separate tables and linking them with a foreign key to the resulting common? Or leave the fields of both services together, and choose the necessary ones based on typeId? Write it in the answers, I will give you a solution, thank you! - Alexander
  • I hurried, the typeId will contain values ​​of type - (translation, translation_and_assignment)? But for certification and for translation it should be possible to specify the performers, separately for certification and separately for translation, also these services have different prices, there can not be a price for translation + certification. - Alexander
  • and what's the problem? You have prices in a separate Service table. You just need to add the ServiceInfoID field to the Order_Service table (this is PK from the common table TranslationInfo and NotarialInfo) and then everything will fall into place, you will not have to touch the rest - Konst

3 answers 3

As far as I understand, foreign keys with cascading constraints will help you (constraint foreign key) - you can change the value in one "main" table, the values ​​in the other "dependent" tables will change automatically. The architecture can be left current.

  • AntonioK, one of the tables "TranslationInfo", "NotarialInfo" is assigned to the main, in the dependent table the repeating fields are marked with these keys, and will change if these fields change in the main table, did I understand you correctly? - Alexander
  • Yes, like this. I do not urge you to immediately start implementing - look through the mana, study the possibilities of foreign keys limitations. This is just one of the possible tools. - AntonioK
  • Thank. This is a method, rather to correct an existing one, I would like to change the structure so that I would not have to use such methods later. - Alexander

You need to optimize the places that really create problems. In many cases, developers deliberately allow for some data redundancy. The criterion may be the following: if redundancy helps simplify the most frequent operations, let it be!
The "extra" tables will surely complicate the queries, and the "extra" fields are more likely to simplify.

I do not know your subject area, in my opinion:

  1. In the table of services performed it would be logical to place a direct link to the artist. Intermediate table Employee_Order_Service here superfluous.
  2. Is it necessary to keep separate tables for translations and for assurances? It may be sufficient to have one table indicating the type of service (link to Service) and the full set of fields required for all services. Let some of them in some cases not used.

    I would make just such a simple scheme: enter image description here

    The order may contain several texts, each text may be translated into several languages. Each job is done by an individual employee. The scheme is certainly banal and does not take into account all your specifics ...