I'm creating a service ticket app.
I have tickets that I can assign to tech(s). I'm trying to come up with an efficient way to change tech assignments.
Tables:
Techs
Tickets
Assignments - Holds AssignmentID, TechID, TicketID
Right now, the best I can come up with is every time the ticket is updated with new tech assignments, I delete all current assignments for that ticket, and create new ones based on the current selections.
This seem stupid and my AssignmentID, being auto incremented, will get huge as we are prone to changing tech assignments often as new work comes in.
I have no idea how to only apply changes: Delete assignment for removed tech (if any) and add new tech assignment (if any).
I'm picking techs from a muli-select drop down. Then I want to hit an apply button to apply tech assignments. I considered modifying the DB for each check action, but if I change my mind a few times during the selection, I end up with unneeded adds/deletes.
VERY new to SQL, so sorry if I'm not talking DB very well. Also, your answer has to be super simple.
I'm using a 'low code' app builder program. I can do java script in it for customization. . . which I know very little of as well.
SQL question
SQL question
That's basically correct.
Like you said, you could get more advanced, and if you have 4 assignments for a given ticketid, and change all of them, and have 5 with the new assignment set, update the 4, then insert a 5th. But that can be complicated (if the new assignment set has fewer, thene you'd have to delete extra old ones, etc). The current way is cleaner/simpler. Wrap it in a transaction to be more databasey.
Possible alternatives:
1) TicketAssignment: Holds TicketID, and then TechID and AssignmentID, but AssignmentID is PER TicketID. TicketID + AssignmentID combine to be the PK.
2) TechAssignment: (similar to above, but reversed ticketid + techid)
Does AssignmentID being big... matter? An INT field will go to 2Billion.
Like you said, you could get more advanced, and if you have 4 assignments for a given ticketid, and change all of them, and have 5 with the new assignment set, update the 4, then insert a 5th. But that can be complicated (if the new assignment set has fewer, thene you'd have to delete extra old ones, etc). The current way is cleaner/simpler. Wrap it in a transaction to be more databasey.
Possible alternatives:
1) TicketAssignment: Holds TicketID, and then TechID and AssignmentID, but AssignmentID is PER TicketID. TicketID + AssignmentID combine to be the PK.
2) TechAssignment: (similar to above, but reversed ticketid + techid)
Does AssignmentID being big... matter? An INT field will go to 2Billion.
It's not me, it's someone else.
SQL question
If I never look at the key, no. But it bugs me that there are gaps in there. . .
Also, thanks for the suggestions. I'll stick with the easy first solution since you didn't come back with "Ya, that's crazy stupid and should never be done".
What's a transaction? I googled it. Seems applicable and not too hard. Seems like over kill given the simple task at hand, but also seems like something that should be learned and practiced for when it really would count.Wrap it in a transaction to be more databasey.
SQL question
It's a pretty normal pattern for many-to-many-relationships. Sometimes that middle table doesn't even have it's own surrogate key (AssignmentID in your case). It just has the TicketID + TechID fields. Then you could delete by TicketID and then insert, and never worry about the AssignmentID growing. Given that AssignmentID has no other attributes, it's currently a weak entity, meaning it doesn't need to be represented exclusively. Of course some people might add date_created or date_updated type of fields for record keeping.
The Transaction wraps up multiple SQL statements so that they either 1) ALL execute or 2) NONE execute. So if any of the INSERTS had an issue, the DELETEs would rollback/be canceled. But may be overkill for you.
It's not me, it's someone else.
SQL question
That's what I gathered when googling 'transactions'. And ya, for just changing techs, it's probably not needed. But when the form is fully built, we could be changing ETA, techs, scope of work adjustment, material list, etc, etc. Now it becomes more important that when apply is hit, all of the changes happen, or the update is aborted so errors can be fixed. Which would be the next big thing. . . error handling.
SQL question
The tool I'm using has an AI you can use to ask questions. It's really quite helpful. I can often enter something like:
Why am I getting "this error message" with this code "copy/paste of a chunk of code". More often than not, it will come back with a pretty good explanation:
Well, it looks like you have a table called 'this' and you are trying to do 'that'. This is how you do that. Here's an example using your code.
I can then copy/past their code example.
It's not a 100% though. There was one problem where it couldn't quite get it right. I would say something like the above, but it's code example wouldn't work. I'd reword the question, and get a different answer with completely different syntax for the function/command. And that still wouldn't work. Then I'd do more research and figure out another way to ask, then it would get it right. But far more often than not, it can see what I want to do, and then tell me the correct way to do it.
This might be pretty common out there, but it's the first time I've seen it. I'm impressed.
Why am I getting "this error message" with this code "copy/paste of a chunk of code". More often than not, it will come back with a pretty good explanation:
Well, it looks like you have a table called 'this' and you are trying to do 'that'. This is how you do that. Here's an example using your code.
I can then copy/past their code example.
It's not a 100% though. There was one problem where it couldn't quite get it right. I would say something like the above, but it's code example wouldn't work. I'd reword the question, and get a different answer with completely different syntax for the function/command. And that still wouldn't work. Then I'd do more research and figure out another way to ask, then it would get it right. But far more often than not, it can see what I want to do, and then tell me the correct way to do it.
This might be pretty common out there, but it's the first time I've seen it. I'm impressed.
SQL question
Did my first field test of my app yesterday. I wish I hadn't taken so long to do this. It's nice having something that's collecting all of the data I want, in the way that I want it, instead of shoehorning everything together with different stuff that's not a perfect match. That being said, the tools I'm using haven't always been available, so there's that.
So far, the feature requests out number the bugs, which I'm calling a win. I'd rather be working on adding more stuff, than fixing broken stuff.
Will be working on getting it on some version of AWS next week. With them hosting, I'm hoping location tracking works. Right now, the phone will not share my location with my home hosted server. Stupid security crap. . . They are looking for a legit host/server. I'm hoping an AWS hosted app will cover that. I may have to work on an SSL thing for HTTPS as well. The location tracking works, because I've tested it on my PC. Local Host is a valid server address where location sharing is concerned. Just need to be able to request the permission on the phone.
So far, the feature requests out number the bugs, which I'm calling a win. I'd rather be working on adding more stuff, than fixing broken stuff.
Will be working on getting it on some version of AWS next week. With them hosting, I'm hoping location tracking works. Right now, the phone will not share my location with my home hosted server. Stupid security crap. . . They are looking for a legit host/server. I'm hoping an AWS hosted app will cover that. I may have to work on an SSL thing for HTTPS as well. The location tracking works, because I've tested it on my PC. Local Host is a valid server address where location sharing is concerned. Just need to be able to request the permission on the phone.
SQL question
Ok. I'm using MYSQL.
I have two multiselect widgets that store your selections as an array. One selects status, the other selects techs.
So the first could have the value [O, C]
Second could have [1, 3]
I need to select records from tableA where status equals O or C and techs contains 1 or 3.
The techs field is a string with the format [1] or [1,2,3] if multiple techs are assigned.
So I want to select all Open and Closed tickets that were assigned to techs 1 and 3.
I have right now:
But it returns no records, and I know I have records that meet the criteria.
ShowStatus is the status multiselct widget, MultiSelect1 is the techs multiselect widget. {{MultiSelect1.selectedOptionValues}} would be passed as = [1, 3]. I'm explaining this because I don't know if this is an Appsmith thing, which you may not know, or universal to javascript/SQL, which you would probably know.
If I remove the AND portion of the WHERE clause, the query will return all Open and Closed tickets. When I add the AND portion, it returns no records. I 'think' it has to do with the "assigned_techs' (which is part of the set created in the first part of the WHERE clause) being inside the {{}}. But I'm not sure, and I don't know how to get it outside of them.
It might also be because the AI is misinterpreting when is say 'contains' for the techs portion of the query and looking for 3 instead of checking if [3] or [1,2,3] contains 3. I'm not leaning towards this. . .
SOOOO much of my app is written by AI, that I don't really know what's going on.
I get the logic of it all, but I'm getting the syntax from AI.
I got the above code by asking it:
"A MYSQL query that selects records from TableA where status column = items selected in multiselect1 and the techs column contains items selected in multiselect2".
I did all the inner joins on my own.
I have two multiselect widgets that store your selections as an array. One selects status, the other selects techs.
So the first could have the value [O, C]
Second could have [1, 3]
I need to select records from tableA where status equals O or C and techs contains 1 or 3.
The techs field is a string with the format [1] or [1,2,3] if multiple techs are assigned.
So I want to select all Open and Closed tickets that were assigned to techs 1 and 3.
I have right now:
Code: Select all
SELECT tickets.sow, tickets.idticket, tickets.po_number, tickets.eta, site_company.name, site.address, site.site_number, customers.customer_name, customers.customerID, tickets.siteCompanyID, tickets.siteID, tickets.assigned_techs, tickets.status, tickets.projectCode
FROM tickets
INNER JOIN site_company ON site_company.idsite_company = tickets.siteCompanyID
INNER JOIN site ON site.idsite = tickets.siteID
INNER JOIN customers ON customers.customerID = tickets.customerID
WHERE tickets.status IN ({{ShowStatus.selectedOptionValues.map(s => `'${s}'`).join(',')}}) AND ({{MultiSelect1.selectedOptionValues.map(t => `FIND_IN_SET('${t}', assigned_techs) > 0`).join(' OR ')}})
ORDER BY tickets.etaShowStatus is the status multiselct widget, MultiSelect1 is the techs multiselect widget. {{MultiSelect1.selectedOptionValues}} would be passed as = [1, 3]. I'm explaining this because I don't know if this is an Appsmith thing, which you may not know, or universal to javascript/SQL, which you would probably know.
If I remove the AND portion of the WHERE clause, the query will return all Open and Closed tickets. When I add the AND portion, it returns no records. I 'think' it has to do with the "assigned_techs' (which is part of the set created in the first part of the WHERE clause) being inside the {{}}. But I'm not sure, and I don't know how to get it outside of them.
It might also be because the AI is misinterpreting when is say 'contains' for the techs portion of the query and looking for 3 instead of checking if [3] or [1,2,3] contains 3. I'm not leaning towards this. . .
SOOOO much of my app is written by AI, that I don't really know what's going on.
I get the logic of it all, but I'm getting the syntax from AI.I got the above code by asking it:
"A MYSQL query that selects records from TableA where status column = items selected in multiselect1 and the techs column contains items selected in multiselect2".
I did all the inner joins on my own.
SQL question
Reworded the question to the AI and got a different answer. Funny thing is, I asked the question, and it gave me an example with pseudo code. Which might work for you guys! But not me. I copy/pasted the same exact question, and it gave me actual code. So it picked up that I need a 'better' answer. The following WHERE clause worked:
I spoilered it in case you wanted to give it a try, and see how it compares.
My thoughts:
I spoilered it in case you wanted to give it a try, and see how it compares.
My thoughts:
SQL question
So that LIKE clause could cause false positives. Unless they are people mapped back to the text value. Or you never have more than 9 techs.
If your tech field is a string array:
[1]
[1,2,3]
[4,11]
What happens if you do like %1%? Well, you get all 3.
Typically, you would have a separate table for the tech assignments, not a single column, that would contain the TechID and the AssignmentID (or OrderID, TaskID, whatever)
If your tech field is a string array:
[1]
[1,2,3]
[4,11]
What happens if you do like %1%? Well, you get all 3.
Typically, you would have a separate table for the tech assignments, not a single column, that would contain the TechID and the AssignmentID (or OrderID, TaskID, whatever)
It's not me, it's someone else.
SQL question
Good catch.
I do have a separate table for tech assignments. This is the only duplicate in all of my data. . . I figured it would be easier to also have this here, so I don't have to open up yet another table, and create an even more complex query. But, you point out a pretty big flaw, so I'll be revisiting this. Or, we'll have tech letters instead of tech numbers. I'll never have more than 26 techs!
I do have a separate table for tech assignments. This is the only duplicate in all of my data. . . I figured it would be easier to also have this here, so I don't have to open up yet another table, and create an even more complex query. But, you point out a pretty big flaw, so I'll be revisiting this. Or, we'll have tech letters instead of tech numbers.
I'll never have more than 26 techs!SQL question
How do you store your time stamps? I have all of my dates/times stored as a string in MM/DD/YYYY hh:mm a format. This has worked for me so far, but I read where someone reported a bug with how the datapicker widget was storing the date/time in "isodate" format as "That's what most people use" and the issue (date format was changing depending on how you used the datepicker widget) was a 'feature' not a bug.
I use the format above because it's what is commonly used here. So, the question is:
Should I be storing it in some iso format in the DB, but then converting/displaying it in the format I want? Obviously they are being lazy and calling inconsistent behavior a 'feature' or 'design choice' because only sometimes forcing an isodate, but not always, is stupid, and should be a bug. For the record, they didn't close the report, they just changed the status to UI enhancement and lowered its priority. "I'm estimating only 30% of users need this". But should I pay attention to the "Most people store as isodate"?
I use the format above because it's what is commonly used here. So, the question is:
Should I be storing it in some iso format in the DB, but then converting/displaying it in the format I want? Obviously they are being lazy and calling inconsistent behavior a 'feature' or 'design choice' because only sometimes forcing an isodate, but not always, is stupid, and should be a bug. For the record, they didn't close the report, they just changed the status to UI enhancement and lowered its priority. "I'm estimating only 30% of users need this". But should I pay attention to the "Most people store as isodate"?
SQL question
Well, I use SQL Server primarily, so I just store as a datetime, and don’t worry about formats. Things that talk to DBs should be datetime aware, and handle it.
It's not me, it's someone else.