1

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:

enter image description here

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:

Proposal for new structure

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?

  • I don't have time to answer, but it seems to me that what a customer orders is not in fact a single 'product' but a collection of order line items (think, the items listed on their receipt or invoice). On the other hand, for the sizes I would say yes you should have three db records for the different sizes, but also some new field that S M L have in common for a given product. – AakashM Mar 22 '16 at 11:41
  • @AakashM Wow. Your comment was incredibly useful. Especially your example with the items listed on the receipt. I've changed my structure accordingly and updated my question (it was to big to fit in here). I think this solves my problem. Hopefully you can read the update and if you think this'll work, please post your comment as an answer. Otherwise I'll keep on searching! Thanks either way – A sneaky ginger Mar 22 '16 at 12:35
  • Make sure you understand table inheritance. Also read Silverston's Data Model Resource Book vol 1. – Neil McGuigan Mar 22 '16 at 19:04
0

I went with the second structure posted in the question, based on the comment of @AakashM . It seemed to be a good solution though I wasn't 100% certain it would last, stay fast and wouldn't flood the tables with abundant data. I've been using it for a few weeks now and it seems to be handling itself just fine. Around 20.000 product records, around 10.000 (combined) Topping, Extra and Sauce records and around 60.000 ProductSizes.

I know this is not really a thorough test and I guess I'll only see what impact this structure will have when I have 100.000's of records but for now it seems to be working just fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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