CODEWITHSUNDEEP
databasenormalization
user1725619
user1725619

Reputation:

Does that violate the 3NF?

I need to put Shipping address, Shipping postcode, Billing Address and Billing Postcode into one Customer table.

Does that violate the 3NF?

Upvotes: 0

Views: 490

Answers (2)

Tomalak
Tomalak

Reputation: 338188

Does that violate the 3NF?

Very strictly speaking: Yes.

Practically speaking: No.

If you don't plan:

  • to have dedicated "Address" entities in your database (i.e.: when the address is nothing more than free text attached to a customer record)
  • to have a variable amount of addresses per customer (but only the two)
  • to have to write a LEFT JOIN for every address you want to select...

...then stick with your design.

Normalizing the address into a separate table is about as sensible as normalizing first and last names into their separate tables just because they tend to repeat. Don't do it.

Consider "practical"

SELECT
  CustomerId
  CustomerName,
  COALESCE(BillingAddress, ShippingAddress) AS BillingAddress,
  COALESCE(BillingPostcode, ShippingPostcode) AS BillingPostcode
FROM
  Customer

vs. "fully normalized"

SELECT
  c.CustomerId
  c.CustomerName,
  COALESCE(bill.Address, ship.Address) AS BillingAddress,
  COALESCE(bill.Postcode, ship.Postcode) AS BillingPostcode
FROM
  Customer AS c
  -- join customer-to-address-resolution for billing
  LEFT JOIN CustomerAddress AS custAddrB ON custAddrB.CustomerId = c.CustomerId
                                            AND custAddrB.AddressType = "Billing"
  LEFT JOIN Address         AS      bill ON bill.AddressId = custAddrB.AddressId
  -- join customer-to-address-resolution for shipping
  LEFT JOIN CustomerAddress AS custAddrS ON custAddrS.CustomerId = c.CustomerId
                                            AND custAddrS.AddressType = "Shipping"
  LEFT JOIN Address         AS      ship ON ship.AddressId = custAddrS.AddressId

Plus, the latter system gives you the headache theoretical ability of having multiple shipping addresses per customer.

The greatest benefit of the latter system is: If they ever change the name of street, all you would need to do is update a single record. In other words: There are no benefits.

Upvotes: 1

reox
reox

Reputation: 5217

yes, because you will create redundancies with these addresses or you need to have NULL values in the database (e.g. when shipping and billing addr are the same) which is forbidden by 3NF. better use something like this:

table customer
... | ... | shipping_address_id | billing_address_id |
----+-----+---------------------+--------------------+
 x  |  y  | 23                  | 24                 |
 x  |  y  | 25                  | 25                 |
...

table address
id | address | postcode | city | country |
---+---------+----------+------+---------+
23 | aaaa    | 1234     | foo  | bar     |
24 | sdfsd   | 2345     | sdf  | sdf     |
....

now you can get the addresses via join

Upvotes: 1

Related Questions