0

I have this Menu for a restaurant with food categorized into different groups i.e.:

For the Breakfast (food here) Salads (food here) Cold Beverages (drinks here) Hot Beverages (drinks here) etc. ...

I've already created a table for the menu, I want to list the food served under Breakfast, Lunch, Dinner, Deserts, Drinks (hot and cold), Alcoholics, Smokes (shisha, pipes, cigarettes , etc..)

SQL has no function to serve my needs so... who has a creative idea!.

UnkownReality
  • 130
  • 13
  • 1
    sql does not have the concept of sub_tables - perhaps you mean related tables.. – P.Salmon Oct 21 '21 at 11:16
  • @P.Salmon, not quite sure if related tables will help me in this! but if I was wrong, could you explain how would related tables do the job for me? let's say a customer wanted to check only what Hot Beverages the restaurant serves, I want the client(employee) to be able to quickly hover through the menu showing only servings that fall under the Hot Beverages. Of course, this can be done by adding the category title to the table and making my way around with indexing skipping the index of the row holding the title, but if the restaurant edits the menu ONCE everything will be scrambled. – UnkownReality Oct 21 '21 at 11:33
  • What would you put in the sub-tables, or what sub-categories would you use? How are you planning to select from your table only Hot Beverages, for instance? – Isaac Bennetch Oct 25 '21 at 15:25
  • @IsaacBennetch, subtables/subcategories will be breakfast, hot drinks, cold drinks, etc. – UnkownReality Oct 25 '21 at 16:20
  • I'm a little confused then, what are the primary categories going to be? – Isaac Bennetch Oct 25 '21 at 20:04

1 Answers1

1

Of course, this can be done by adding the category title to the table and making my way around with indexing skipping the index of the row holding the title, but if the restaurant edits the menu ONCE everything will be scrambled.

That sounds like you're thinking of an SQL table like it was a Word table with different kinds of rows (like heading rows and data rows). That's not the case; an SQL table's rows should all be alike.

If your menu table is something like

id name price
1 Omelette 8
2 Sandwich 6
3 Soup of the Day 8

the simplest way to categorize these is to add a new column for the category:

id name price category
1 Omelette 8 Breakfast
2 Sandwich 6 Breakfast
3 Soup of the Day 8 Lunch

(though in a real database design the category would probably be a foreign key to a Categories table)

You can then use a WHERE category = ... clause to only show some entries, or do SELECT DISTINCT category FROM menu ORDER BY category to get all of the categories in alphabetical order.

AKX
  • 152,115
  • 15
  • 115
  • 172
  • 1
    I would definitely make the category a foreign key in this case, otherwise the likelihood of inconsistent data is quite high. Not to mention it follows the best practices of database design, but my primary motivation would be to keep the data consistent. – Isaac Bennetch Oct 26 '21 at 14:14
  • Yep, but for starters just a category name column would be enough to do what OP wants, namely being able to see the categories and choose from them. – AKX Oct 26 '21 at 14:24
  • Thanks @AKX u helped me out, but after reading Isaac's comment i went with the foreign key approach. thank you. I would be a waster of time effort and money plus ink if any printing was needed. :) – UnkownReality Jun 12 '22 at 10:26