0

I would like to simplify a Many-to-Many Relation complex, composed by key with 4 fields, one of which is used to establish dependency (serie: is order of creation).

I know that should break the table and keep dependencies, but after reading and thinking a long time, I do not know how.

Below system that contains the problem, which is represented in-First Normal Form 1NF-, where Rel_Apply_Lodge it is the table showing the problem.

Apply:

[Key] id_applies

Rel_Apply_Lodge:

[Key] [FK] id_applies

[Key] [FK] id_lodging

[Key] [FK] id_kind_boarding

[Key] [extra field] serie <- solves duplicate rows issue

Lodging:

[Key] id_lodging

[FK] id_kind_booking

[FK] id_municipality

Salvatore Roncone
  • 7
  • 4
  • What's the situation you're trying to modelize?? – Goon10 Jun 05 '14 at 09:45
  • **Target:** To manage request (apply for) of lodging/hosting. **User's history:** A person apply for lodging, and in this very moment a relation (Rel_Apply_Lodge) is created. This relation stored: id_apply, id_lodging and id_kind_boarding. – Salvatore Roncone Jun 05 '14 at 10:09
  • **Issue:** For each one request is stored one register. In case a person how need to apply for three days, and by same hosting, is when the system stored the same three rows. It's when the serie/order (field) _keeps dependencies and avoids duplicates_. – Salvatore Roncone Jun 05 '14 at 10:20
  • why don't you add `number of days` or `duration` as an attribute in the `Lodging` table. – Goon10 Jun 05 '14 at 10:27
  • because `number of days` it is independent of the `Lodging` entity. `Lodging` manages the accommodation available to users. In the event that the property `number of days` was added, should be done it in other entity. (Why? Because when you have accommodation to be rented, don't specific `number of days`, this will be choosen by the user) – Salvatore Roncone Jun 05 '14 at 10:50

1 Answers1

0

It easier and trivial than it had ever imagined.

To solve it, separates a Many-to-Many relation in two: One-to-Many.

The unique conditions to achieve succesfully are:

  • Maintain the same fields that had Many-to-Many relation.
  • And, add in both entities an ID attribute. These keeps dependencies and solves duplicates issue.

The model looks like this:

Apply: [Key] id_applies

Rel_Apply_Day: <--NEW

[Key] id <--NEW

[FK] id_applies

serie <- we can keep the serie indicator

Rel_Day_Lodge: <--NEW

[Key] id <--NEW

[FK] id_lodging

[FK] id_kind_boarding

Lodging:

[Key] id_lodging

[FK] id_kind_booking

[FK] id_municipality

Salvatore Roncone
  • 7
  • 4