Help understanding the database structure for Orders

#1

Hello guys, I need help regarding SQL server. I connected Samba to SQL server 2016 and so far its been easy and stress-free. Until I recently opened up my orders table(Basically, I set up a Pizza shop and to make things easier, I set up order tags as the crust and each crust has a separate price. the product its been mapped to has no price

. and whichever crust is chosen, the order tag updates the product with its price
.
Going to my orders table in sql, the product price is showing zero even though on Samba its been updated.
I have tried to join the order tag table and order tags but its not even showing any connection between the order tags and orders.

#2

So it’s working correctly and you think it’s broken?

If you pull that ticket up after submit it still shows the correct Price right?

Not all data is stored 1 for 1. A lot of it is json
Also some is relational data for example a single ticket is not stored in one database table it may be spread across multiple parts that makes the ticket

#3

@kendash…lol ofcourse its not broken…Im looking for help on what I might have done wrong or if its normal. To the best of my knowledge, every successful order can be seen on the order table in sql server studio, right?? I was just expecting the price in sql to be the updated one not the product price

#4

How could you have done anything wrong unless you manually edited the database did you manually change the database schema if not then nothing’s wrong you just need to study how its storing the tables if that’s what you’re getting at

#5

Perhaps you don’t understand but all data is stored in sql all of your configuration date is in Sql it’s not in SambaPOS program it’s in sql so sql server is working correctly if it was not you wouldn’t be able to place the order what you need to do you need to study it and look at the table relations to understand it

#6

nope changed nothing on the schema…Im using a BI tool to view the reports instead of using Samba and its pulling the wrong price even though I specified the right table.

#7

There is nothing wrong with it you just don’t understand the relational setup of how the database is working spend some time to study it before you make your conclusions that it’s not working

#8

okay…Never said it wasnt working, thought someone might have noticed something like this and could give insight. Looking at table relations is what I have studying all week

#9

Your title of your topic implies that it’s not working you might want to change that if that was not the intention that’s certainly how I read it however I’m driving when I get home I will give you an explanation of what you’re asking about the database I’m just not where I can do that right now

#10

I’m not sitting in front of it right now so I cannot be certain but I if if my memory is correct I believe that data is stored under the tickets table and it’s stored as Json data

#11

okay thanks…wasn’t my intention at all…Thanks I will be waiting

#12

So I was wrong… it is in Orders table under OrderTags column.

Order Tags price is under OrderTags table in Price column

#13

@Jesse yh I had seen that but this is how my table looks

look at the highlighted portion…my order tags column has the price

1 Like
#14

Are you looking for someone to explain how the DB works so that you can do analysis with a 3rd-party tool? You will need to figure that out on your own…

This is not a matter of “anyone notice this before”? Because, no, most people will never look at the DB. They don’t need to. That’s what the Custom Reports are for. For those more savvy in the ways of the DB, sure it can be done, but it is not something that any help or support is provided for. Users are not meant to look into the DB directly.

If you have a zero-priced product, you can clearly see the price column will be zero. That is how it works. The Product Price is the Product Price. The only way it will change is if you change the price of the Product itself, or use a Price Change Automation Button.

For what you are trying to achieve, you need to read and parse the JSON-formatted OrderTags column, add the values, optionally add the Product Price (zero in this case), and display the results (which is the total Order Price) in your Reporting Tool.

1 Like
#15

I changed the title of this discussion to something more appropriate. The original title would confuse readers and they would think something was wrong with SambaPOS Database when there clearly is not.

1 Like
#16

@QMcKay Im trying to understand the functionalities of Samba and as someone with little db experience, Looking into the db was naturally my first start. I understand the concept of product price. i just felt since its a completed transaction, the order prices should be reflected into the order table and not the original product price. Using a 3rd party tool was to help me with my sales. dont know why i need to explain all this…as this is a forum where people come to talk about issues. I quite appreciate all the help you’ve given to me and others but chill on the attitude

#17

I can respect that idea but its really not a good idea. Looking into the database would certainly confuse anyone. If your looking to understand how SambaPOS works then you should dive into it and practice with it. Try some reports etc. Let it handle the database.

Typically people dont start looking into how the database works unless they want to manipulate something. Perhaps your topic title is what set us off on the wrong avenue. Your title clearly said that there was a problem with the SQL database. Your first discussion implied that as well. In fact your entire discussion really doesn’t explain what your really looking to accomplish. Most readers would think you were trying to report something not working correctly with the database.

Lets start over and a good starting point would be you explaining what you really are looking for. Why do you need to know how the database works specifically with orders? You did not ask about any other part of the database but specifically order price. This leads me to think your trying to do something specific with orders via third party tool or something. Explain that maybe we can help you there.

#18

If that is what you think then I guess we’re done here. Have fun.

1 Like
#19

@Jesse I wasn’t trying to manipulate anything, i just noticed it when using another tool to report on Samba which made me curious. Posting it was all to satisfy my curiosity and gain more knowledge to better understand Samba.

1 Like
#20

Thanks mate @QMcKay if I have another issue, I will be sure to look you up