2nd Normal Form ERD thoughts

Question

This ERD has completed 2nd Normal Form normalisation, as every table has a primary key, the data is atomic, there are no repeated groups, and there is no partial key dependency.

What are your thoughts?

I don't know whether to scrap the PostCodeAndCity table and include the City data in the address table, which was suggested by my study materials.

Answer 1

The biggest issue is the orders table. As it stands, a user can make a single order only. You will need multiple order_ids for purchases of many products. What you actually require is two tables: one to keep the total of a purchase (think of it as all the meta information you find in a receipt) and one table that contains the products and quantities associated with a specific order_id. You also want to maintain a price on the purchased product, since prices change on the catalog products.

Funny thing, our book deals exactly with the same database problem and focuses on design. It's in early access so I don't have the 5th chapter live which would show the diagram but you can see the requirements analysis here just to get an idea: https://livebook.manning.com/book/grokking-relational-database-design/chapter-3/v-1/point-18510-126-133-1

Answer 2

postalCodeCity:

postalCode PK
city

postalCode    city
-------------------
1234          abcd
5678          lmnop
8900          xy123

address:

addressId PK
propertyNo
street
postalCode (associated with the postalCodeCity.postalCode)

addressId    postalCode
------------------------
1            5678
2            1234
3            5678

I am unsure of whether to scrab the PostCodeAndCity table and include the City data in the address table, however this was suggested by my study materials.

Having a postalCodeCity table ensures that a city and postal code are related consistently.

---

Another thing to note is that orders are typically modeled as orders and order lines - that is two entities. A customer is associated with an order. An order has info like order id (or number) and this identifies an order uniquely, order date and customer (who had placed the order). And, an order has multiple lines. Each order line has order line id, order id, product id and order quantity. This is a general concept of customers and orders.

Answer 3

• Having a surrogate ID as a primary key neither identifies a row nor enforces any uniqueness regarding the actual data. There is nothing to restrict you from having multiple products with the same name, customers with the same full name, duplicate email addresses, etc. You must identify the actual keys which make each row unique and, if you insist on keeping the surrogates, then mark the actual keys as Alternate Keys (AK).
• Practice a standard modeling method if you are serious about it.
  • IDEF1X Introduction
  • ERwin Methods Guide
  • SO post
• There is no reason to deal with physical concerns like datatypes at such an early modeling stage. I understand that it is a practice you see everywhere in books and online, but did it ever offer any real value to you? Additionally, remove the useless prefixes from everything. Even if there was a reason to have this kind of prefixes in the physical implementation, which I don't think there is, all the noise is especially detrimental at this stage.