0

Field of type JSON

Hello Friends,

As I make a database app for my own food business, I am in the process of designing the menu table. The menu items come in varied units like (piece, container, plate, meal etc.).

I have two other tables (Units and 'Container Types'). Units has all the Unit types  like piece, container, plate and meal and 'Container Types' has all the different types of containers like 250ml, 500ml, 750ml and 1000ml.

The problem I am facing in designing these tables is that I can have a separate table named Rates where I can store the rates of menu items that come in different container types, however I can't store the rates of such items that only have a single Unit type eg: Piece or Plate.

Ideally the rates would be just a another column in the Menu table, but in the case of items that come in containers, I will have to make multiple entries of the same menu item eg:

1. Makkhan in 250ml rate is $x

2. Makkhan in 500ml rate is $y and so on..

This will create multiple entries of some items in the Menu table which doesn't seem to be a good database architecture design. On the website we can put these as subitems with the rates for the end customer to choose, but I can't figure out how can we enter these as records in a table for the database user/manager who has to do the billing for the customer.

One solution that comes to my mind is to have the 'Unit Type' fields as an object type (JSON), but I don't know if that is even possible. Even if it is possible then JSON object will have different Key-Value pairs for different items (eg: For items of type Container, there will be 4 Key-Value pairs and For items of type Piece or Plate, there will only be 1 Key-Value pair). This can been seen in the excel sheet above.

Any suggestions please !!

3 replies

null
    • Alain_Fontaine
    • 4 mths ago
    • Reported - view

    Maybe something like: https://forum.ninox.com/t/g9hrzlf/filling-lines-of-ordersarticles-with-dynamic-multiple-choice-fields-and-cascading-dynamic-choice-fields

    • Fred
    • 4 mths ago
    • Reported - view

    My answer to your immediate question about rate is to create a subtable to Menu called variations, or whatever makes more sense to you. Then it is in this table that you track Units/Container Types/Rates. In fact Units and Container Types could be just simple choice fields if they don't change very much

    Now to make things more complicated. You may want to consider renaming Menu to Product. Then create a new table called Menu that links to the variations subtable. Why? You want access to the variations so you can show that in your Menu. If you link to Products you can't see the subTable. You will probably need to track things differently between what you produce and what your actually advertise to the public. Somethings will come and go and now you don't have to permanently delete them from the Menu. You can also create special menus without affecting the "main menu".

    You can also now track inventory of each Product.

    You can also build an ingredients table that can link to the Product table so you can track the back end of your business.

    • Alain_Fontaine
    • 3 mths ago
    • Reported - view

    I  created a small demo database showing a possible solution. It is not fully polished, of course. There are tables fir the Menu items, for the Units and for the Details, and then a table where all the possible combinations are defined, with their price. From the Dashboard, which is meant to contain a single record, one can open, populate and close orders.

    Test Vermaji.ninox
Login to reply

Content aside

  • 3 mths agoLast active
  • 3Replies
  • 98Views
  • 3 Following