Relationship Identifying Vs No-Identifying

Question

Relationship Identifying Vs No-Identifying

Navigation

#1 by (1 votes)

0

First of all, I told you that I have already read several similar questions with "technical" answers that look like C & P. What I need is a clear example. The normalization is 3NF.

In this project, in the administrative panel, you have to create cities and zones and each zone has to belong to a city. Also create hotels and assign them in the corresponding areas, and finally create aliases for each particular hotel, since people call the same hotel with different names. The hotels and hotels_alias tables are for filling an autocomplet input.

The calculation of the price is made according to the service (standard, private and VIP) depending on the area and according to the number of passengers and the season, I still do not believe the logic nor the tables to calculate the price per passenger and season, that's why they do not appear in the diagram below.

A good explanation seems to me this link

However, I have some doubts.

hotels_alias can not exist without the table hotels that in turn can not exist without the table zones and this in turn does not exist without cities. Since a city is divided into many areas, the hotels belong to these areas, areas that are part of a city, and the aliases of the hotels belong to a hotel and can not exist if there is no hotel.

So far it is clear that cities is a strong entity or parent and zones, hotels and hotels_alias are weak entities or children.

In the EER diagram you can see that it has a identifying relationship. The first question is is it correct that despite being weak entities or daughters have their own ID? and That this ID is PK and NN and AI? In some examples, these daughters or weak entities do not have their own ID, therefore, their PK is formed of two FK of the related tables as in an N: N relation (zones_has_servicees).

If in fact weak boards or daughters do not have to have their own ID because they should be able to identify themselves by their strong board or father, then how would they be able to update or delete an area, or a hotel or a hotel? alias?

DELETE FROM zones WHERE name = 'nombre a borrar'

Is the previous form correct? Would you have to create an index to the name column? What advantages, if any, would you have to do it with name instead of your own ID? Is it correct for a weak table or daughter to have its own ID and create a compound station with this ID and the ID of its parent table? Does this type of relationship have any function or is it only for engines like InnoDB and can it perform an ON DELETE CASCADE?

What happens if I have two zones with the same name? for example: Hotel Zone, which both the cities of Cancun and Tulum have that area. To make a DELETE serious ?:

DELETE FROM zones WHERE name = 'nombre a borrar' AND cities_id = ID

Understanding what is a parent entity and a daughter then why WordPress creates relationships like the one below where you can see that it uses a weak relationship with wp_postmeta and wp_posts. It is assumed that a wp_postmeta can not exist without a wp_posts, right? It does the same with comments and users.

mysql

asked by Miguel Alejandro Lazcano 22.10.2017 в 17:12

source

1 answer

How to keep selected a nested combobox data when reloading the page? error with other attributes in devise ruby on rails

score 1 · Accepted Answer

We will start answering your questions little by little, and we will be completing the answer.

TL / DR

Relationships depend a lot on what you want to build and what the system is. And relationships are mostly theoretical and put into practice as appropriate.

Explanation

Let's start with your example on the wordpress tables. The meta tables you see there are daughters tables but they are only tables created to save flags on the parent tables. This is to avoid creating columns infinitely on parent tables. It is not that they can not exist without the father, it is that they are used for something else.

For example, imagine that about a user, they want to save data such as age (keep in mind that it is an example), it could be saved in the main table (adding an age column), or on the target table (a meta_key age and its value). What this table does is to avoid adding columns for each new attribute that you want to show. Also in this case allows the plug in save their own data by creating their own keys.

So, is it a weak board? theoretically yes, but it is practically the same table, only that it has a use to facilitate the aggregation of information and avoid the modification of the scheme indiscriminately.

Your tables

Weak relationships are seen in a partitioned scheme of your table. A weak relationship between two tables does not imply that the table is weak. Moreover, within the system, a weak table can be a strong table for another portion of the relationship.

Keep in mind, that the hotels table can not exist if the zones table does not exist. But, if you separate the hotel table, and only look at hotels and hotels_alias, the hotel table is strong.

For some time now, in practice, the composite keys have been removed from the related tables and keys are used in the tables themselves. See a practical example.

Hotel A is located in Zone 1, Hotel B is also located in zone 1. For some aesthetic reasons, the city has just divided Zone 1 into Areas 11 and 12. And your hotel A, is now in zone 11, and B in 12. You notice that you penalized your tables badly at this moment. One of the hotels, will change one of its foreign keys if or if (the other is saved, just change the identification of the area). With your model, you are penalizing an update in cascade. That does not say anything, because your system is an ABM, not a data warehouse (in a data warehouse it's not a bad idea to copy keys from one place to another, but that's another different subject).

So, why not let your hotel have its own password? Imagine that you want to modify the data of a particular hotel, as Alvaro said. You will not be able to identify it, unless you know what hotel you are talking about, and that is only known with the hotel's own code.

For example, note that carrying the city key everywhere does not make any sense, it does not contribute anything.

As another example, the zone_has_service table has no id. Therefore, when you want to modify something (if it is already, it is a link table, but sometimes things have to be modified), you will have to pass several values ...

Relationships depend on the model you want to build, and how you are going to manage it. Simplifying the model by a matter of accesses and tables is not bad.