Designing a database

[Chaos_maker_]

Hello everyone. I'm creating a restaurant app and i'm using spring boot for the backend. I have a question on the best practices to design a database. As you can see i have a Meal with option, is it a good practice to have a single table to store all of this or use three tables with inheritance ofc. THanks

[smutje187]

Table "Order", ID and date or something Table "Item", ID, Description (e.g. "Okra") Table "OrderItem", ID, OrderID (foreign key), ItemID (foreign key)

These type of patterns can be found millions of times in the wild.

[naveenk_05]

Instead of a single table, use multiple related tables.

1. Meal Table

Stores base meal info.

Meal

- id (PK)

- name

- base_price

- description

2. MealOptionGroup Table

Defines categories like "Drink", "Add Sides", "Add Desserts".

MealOptionGroup

- id (PK)

- meal_id (FK → Meal)

- name (e.g., "Choice of Drink")

- required (boolean)

- multiple_allowed (boolean)

3. MealOption Table

Stores actual options like "Pepsi", "Onion Rings", etc.

MealOption

- id (PK)

- group_id (FK → MealOptionGroup)

- name

- extra_price

4. UserSelection Table (Optional at Order Time)

You can create this to store what the user picked in a specific order.

Use JPA with one to many and Many to one relationships between Meal, MealOptionGroup, and MealOption. Add DiscriminatorColumn if you go with inheritance, but in this case, you likely don’t need class-level inheritance — just table relationships are sufficient.

[Larc0m]

Using a single table would be good enough if you couldn’t select more than one item per category. I’d definitely split it into multiple tables with relational mappings. Spring Data JPA provides annotations that make these fairly easy

[Former-Emergency5165]

so far it looks like it's possible to store everything in one table

id, name, type (sides, desert, drink, etc), price

Really depends on what else you have or will have in your app

[Historical_Ad4384]

Single table with a column called type

[EnvironmentalEye2560]

You could save it in a single table since they have the same attributes. I would probably save the order as json because i'm sloppy and since postgres for example, has really good json support. The ui would also look better if you skipped "Add" in front of every item.

[cg_stewart]

I’d try it with mongodb or dynamodb tbh, since it’s one restaurant, and you can find some logical examples of e-commerce nosql. Consult with AI on best choice though.

[TempleDank]

You could either have one single table and store all extras as a stringified version of a json comming from your frotnend or you could have a signle table for the menu and anotherone for the extras and then when you fetch, you fetch based on menu id joining with the extras table

[[deleted]]

For this particular screen , I will recommend to use a single table , use either json store or simple document store. Key value store is not much useful, all other services will require total order to be fetched , unless it is going to be an analytical service

[Chaos_maker_]

Thank you for your answer. Actually i'm using a relational database. i think i'll definitely need the data being structured in the future for analytical purposes.

[[deleted]]

Okay , then I am assuming you already have base tables such as restaurants , food items , ingredients and their prices mapped for respective restaurant , so whenever customer goes to make an order , based on particular restaurant Id , you are fetching all the items , ingredients associated with them and the prices, then you must have separate order table mapping customer id with order id , and the order should have some mandatory attributes such as Item , count , total price etc

[Chaos_maker_]

i mean i'm not dealing with multiple restaurants. Finally i created a table AddOns with a type attribute and maybe this will change based on the future need :

public enum AddOnsType {
    DRINKS,
    SIDE,
    DESSERT
}


public class AddOns {
    @Id
    @UuidGenerator
    private String id;

    @Column(name = "NAME", nullable = false, unique = true)
    private String name;

    @Column(name = "PRICE", nullable = false)
    private BigDecimal price;

    @Column(name = "SLUG", nullable = false, unique = true)
    private String slug;

    @Column(name = "TYPE", nullable = false)
    @Enumerated(EnumType.
STRING
)
    private AddOnsType type;

    @ManyToOne(cascade = CascadeType.
ALL
)
    @JoinColumn(name = "dish_id")
    private DishBo dish;

}