CODEWITHSUNDEEP
sqldatabase-normalization
Zzub
Zzub

Reputation: 41

Do two tables with the same content break data normalization?

[Assuming there is a one to many relationship between an individual and an address, and assuming there is a one to many relationship between an agency and an address.]

Given the following table structure:

enter image description here

Wouldn't you want to merge the two address tables together and instead of using a foreign key within each one use a tie table? Like this: enter image description here

Are they both valid for normalization or only one?

Upvotes: 0

Views: 83

Answers (1)

iainc
iainc

Reputation: 868

Depends what you want to do.

In your second example with the tie tables, if I want to do a mailshot to my customers then my query has to go out to the agency tie table to exclude any agency addresses.

Of course you could have an address type column to differentiate but then you have a more complex query for your insert statement.

So although "address" is a global idea, sometimes it is easier to have it segregated by context.

Secondly, your customer data would usually be changing much more than your agency data. There may also be organisational and legal requirements around storage of personal data that make it better to separate the two.

e.g. in a health records system I want to be able to easily extract / restrict client data and to keep my configuration or commissioning data separate.

Thus in all the client systems I have used, the model tends to be the first one you describe rather than the second.

Upvotes: 1

Related Questions