0

Options that I can think of.

  1. Subtable which lists each dish in the order with it's quantity with a FK to the main order table
  2. A json representation inside the Order Table with the details of the individual dishes in the order
  3. Pre-calculate all the possible dish combinations and store in a table and then use reference to that table inside the order table.

Which is the best option among these? Are there any better solutions?

Rishin S Babu
  • 1,553
  • 3
  • 13
  • 16

1 Answers1

0

It's not clear whether you want to store these permanently or not.

In memory, I'd have a map. Food order as the key and a list which stores the dishes. I'd then have two lookup tables; one for food orders and one for dishes.

In a database, I'd have something similar. |FoodOrderId|Dish0Id|Dish1Id|Dish0Id| Again you would need two further lookup tables. Limitation with this option is that the order would only be able to contain a finite number of dishes.

If limiting the number of dishes an order an contain is a showstopper, you could store the order within XML;

<FoodOrder>
<ID =##>
<DishId>#</DishId>
<DishId>#</DishId>
<DishId>#</DishId>
</FoodOrder>

You can then put the XML string into a database for storing.

Intern87
  • 469
  • 1
  • 6
  • 18
  • There is no limit to the no of dishes that can be in an order. If it is a database schema, wouldn't putting it xml limit the querying capabilities.. like wanting to look up the users who ordered a particular dish in their order? Was also wondering whether the best solution lie in a NoSql schema-lessness? – Rishin S Babu Jun 23 '15 at 11:54
  • You will be able to search through xml in a SQL table. see [link](http://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column) – Intern87 Jun 23 '15 at 12:32
  • I also wouldn't recommend going down the schemaless path if you can. Obviously this depends on how your data is input. If you have an app like JustEat for example, it should be easy for you to manage the consistency of information. if you are reading in historic unstructured data it may be a little different. Try to validate on input rather than using data quality workarounds used in the big data world. – Intern87 Jun 23 '15 at 12:41