Database Design – Ordering Complex Products

I'm working on a platform in which you can order food from different sellers. Currently the platform is working and restaurants can create food products while clients can order them.

Easy enough.

A restaurant can create 1 or more Product.
A client can browse a list of products and can order them, at which point an OrderProduct will be created containing the id of the product and the number of times it's been ordered.
An order can consist of 1 or more OrderProduct

But now our users are asking to move to a more complex system with more options.

Right now if you've a product in 3 sizes (small, normal, large) you'd have to create 3 separate Product for that. We want to separate it in a table for the Product and a table for the Size. Those will be combined in an association table, ProductSize, which will add an additional cost (small = -1€, large = +1€) to the entity.

Still easy enough, and OrderProduct would contain 1 or more ProductSize entities instead of Product.

But it doesn't stop there, because a ProductSize (or Product?) should also be able to contain an additional supplement, as well as 1 or more additional sauces and 1 or more additional toppings.

I think those would create another 3 tables (Topping, Sauce, Extra) which would be combined together with the existing ProductSize into a ComplexProduct? I'm not sure this is the best possible way of doing things.

I would end up with a structure something like:

Which is very confusing to me. It might not even work.

Additionally, let's assume you have 1 product: a pasta dish (1 ComplexProduct)

You also need to make it available in 3 sizes: small, normal and large (3 ComplexProduct)

You have 10 types of Sauce to choose from (30 ComplexProduct)

But you can also have a combination of Sauce (Too many ComplexProduct).

Now you can also have 10 different Topping and 10 different Extra.

If all these links need to be created in a database, that would result in tens of thousands of combinations for this single product. I don't think this is do-able.

If a customer would want to list the products which he can order, how should I send him these 10.000 combinations? A restaurant has an average of 80 products. That means I'd have to send 80.000 products (combinations). Impossible.

Simply creating these products in the database isn't enough. I need to find a way to create an order out of them using the OrderProduct (or anything equal).

I'm 100% sure I'm seeing everything too complex. There must be a much easier solution for what I'm trying to achieve over here. If anyone can point me into the right direction, that would be awesome.

NOTE: It might already be obvious but I'm having database logic issues in my head from time to time, especially with the relations between tables. 🙂 I'm using Doctrine to handle all my database stuff, so it would help me a lot if someone could explain it by using entities, though not required. If someone could point me out an optimal (need not be perfect, just something that will keep working in the long run) structure using plain, I'll figure out how to translate it to Doctrine myself.

STRUCTURE PROPOSAL:

Based on a comment, I've changed to structure to this:

It simplifies things greatly for me and the way the comment was written opened my eyes.

In this new flow a restaurant can create a Product, in this product it will be able to directly reference Topping, Sauce and/or Extra. These references will stay with this product throughout the entire flow. This Product will be added together with the Size to form a ProductSize, which will be the actual thing a customer will order. These will be added in OrderProduct together with their amount AS WELL as (I forgot these on the included image, sorry!) selected Topping, Sauce and Extra. So these last ones will actually be ordered as separate products, but an OrderProduct will know it can contain these Sauce etc options because of the initial reference through Product. I will also combine the Topping, Sauce and Extra in OrderProduct so that the amount in that table is also applied to them + the restaurants can easily see to which product a certain topping is applied.

This looks an acceptabel structure now in my head. Did I make any mistakes?