I've built a few relational databases in Access, and will now start moving to SQL Server. I want to build a database for me to practice and learn, and thought about building a fake restaurant's database. I've been thinking about the design, and came across an interesting problem.

Each menu item would have a record in one table. Receipts would go on another table, customers too. But how would I join many menu items to a single receipt record?

I would prefer an answer about the design itself rather than the solution in SQL Server.

I know this question might sound simple to answer but I'm new to database design.

Thank you.

  • 1
    You would have another table called "ReceiptItems" which would have a foreign key to Receipts and another to Menu Items. If you need to track multiple customers to one receipt, then you would have another table ReceiptCustomers. Jul 28 '17 at 15:39
  • 1
    This is a many:many relation which requires a 3rd associative table, see en.wikipedia.org/wiki/Associative_entity
    – dnoeth
    Jul 28 '17 at 15:41

What you're describing is called a "many-to-many" relationship. Each receipt can have many menu items; each menu item can be on many receipts.

This is normally handled by creating a "bridge" table.

Let's assume you already have the following tables: menu_item, customer, and receipt. Further, let's assume each one has a primary key column (uniquely identifies a given row), <tablename>_id.

So, each row in receipt would be connected to a customer, and would need to be able to have several menu_items associated with it.

First, you'd add customer_id as a column in the receipt table, to tie the receipt to a customer.

However, you have an indeterminate number of menu items to attach as well. One option would be to have 5, 10, maybe 15 columns, each of which could be tied to one menu item. However, this is messy - you leave a lot of columns empty (even NULL columns consume some space), and you still run the risk of eventually having a customer who orders more than that number of items.

It's much cleaner to add a new table, receipt_item. This table would have its own primary key (receipt_itme_id), and would tie to one receipt (so it has a receipt_id column), and one menu_item (so, add the menu_item_id column). In the case of a restaurant, you might have other columns as well: instructions, to note that the steak should be medium rare, for example. That's just a thought from your example; all a bridge table must have is the ID values from the two tables it connects.

So, each receipt could have anywhere from one to dozens of receipt_item rows connected to it, and each of those ties to a menu_item.

  • Thank you! I had only heard about many to many relationships. I had only used one to many relationships. This is a great solution, thank you! Jul 28 '17 at 23:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.