I'm taking a Coursera course called Advanced Data Modeling and practiced creating a conceptual model as well as a logical ER diagram. The prompt gave me the following fields and said to create a data model using them:
- Order ID
- Client name
- Client address
- Order Date
- Delivery Status
- Delivery Date
- Contact
- Number Email
- Item name
- Item price
- Total cost
Below is the difference between my answer and Coursera's. I'm not as concerned about the attributes that Coursera added and I didn't. My main questions are:
- For the conceptual model, Coursera says "place" in place of my "client info" but the contact info is just as important. How would one determine the best relationship between two tables?
- For the Logical ER Diagram, why would you separate Delivery Status and Delivery Date if it's tied directly to the order?
- For the Logical ER Diagram, I created an Order Detail table because my thinking was you want to separate out each order by all the items within the order. It seems like Coursera's answer doesn't take into account that a customer might order multiple items in the same order. My understanding is that this is going against 2NF and causing redundant data if a customer orders more than one item. Can someone confirm if I'm missing something here?