CODEWITHSUNDEEP
mysqlsqldatabase-design
Ken Weger
Ken Weger

Reputation: 41

trying to figure out a good database design

OK here is my problem I am trying to design a database for a class project. I am having a difficult time with figuring out how to relate one table to another. In this instance i am trying to have a Primary key that can represent several different items. Like a single PizzaOrderID representing both a meat lovers, veggie lovers, and a cheese pizza. all with different quantities of pizzas. Can someone please help me out with how I might be able to set up this design?

Edit:

Third Normal Form

Customer

Customer ID → Primary Key

first name last name street address Apartment City State Zip Code Home phone mobile phone Other phone

Doughnut

Doughnut ID → Primary Key

Name Description

Doughnut Order

Doughnut Order ID → Primary Key

Doughnut ID → Foreign Key

Quantity Unit Price

Order Sales

Order → Primary Key

Doughnut Order ID → Foreign Key

ok this what i had as my third normal form as you can see the exact item i am dealing with is doughnuts i am guessing i need to adjust this form

Upvotes: 1

Views: 598

Answers (1)

Thinkeye
Thinkeye

Reputation: 928

Let's start from the beginning. You have a table representing meals, which you apparently call pizza and this table has a primary key pizza_id. (I use _ for readability). Such meal can be ordered at a restaurant table, by putting an order stored in another table with primary key order_id.

So far so good. Now the order can be for one pizza, two, or more of them. Moreover the order can contain more pizzas of the same kind! Apparently your design needs to cover for this and the solution is N:M (many to many) relation.

This is done in the third table called orderitem with a compound primary key (composed from two columns) pizza_id and order_id. These two are simultaneously foreign keys referring to their respective tables. As additional column can be put amount of the meals, which are of the same kind.

Upvotes: 1

Related Questions