CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
nusje2000
nusje2000

Reputation: 503

Symfony doctrine database normalization

Im working on a database and I have a problem with one of my relations, I have a Person entity and an Address entity and there is a OneToMany relation between Person and Address. I want to normalize the Address entity so if you have multiple Persons with the same Address, there is only one address in the database. Here is a visualization:

Person 1: { id: 1, name: "foo", address_id: 1 }

Person 2: { id: 2, name: "bar", address_id: 1 }

Address 1: { id: 1, street: "barstreet", housenumber: 123 }

If I want to change the address for Person 1 I dont want to change Address 1 because person 2 is still linked to it so I want this to happen:

Person 1: { id: 1, name: "foo", address_id: 2 }

Person 2: { id: 2, name: "bar", address_id: 1 }

Address 1: { id: 1, street: "barstreet", housenumber: 123 }

Address 2: { id: 1, street: "bazstreet", housenumber: 5 }

What is the best way of solving this problem, thanks in advance.

Upvotes: 1

Views: 693

Answers (1)

Daniella
Daniella

Reputation: 129

The structure of you database looks fine. I suppose that you have cases with many people which are living at the same address :)

When you need to update an address of a person, I would do the next flow:

  1. Check if the address with the provided street number, house etc exists in the database.
  2. If that address exists in the database, we associate that person with that address.
  3. If it doesn't exist in the database, a new address should be created and added to that person.

Upvotes: 2

Related Questions