0

best way I can explain the situation in as short as possible:

I am making a repair system for a jeweler company. In simple, I have a customer table and a repair table. The customer table holds customer information and the repair table holds the customers repair information. The repair and customer tables are tied together by the customer.ID column (customer_id in the repair table).

In the system there is the option to view existing repairs for a customer - shows all the repairs that match a specific customer_id.

The client I am making this system for would like a way to tie repairs together. Example, if customer john has 4 repairs in the system. Two repairs are new, and two are completed. If they do not want the customer to be contacted until the two new repairs are COMPLETED, they would view all the existing repairs, check off those two, and click a button that says 'tie repairs'.

I am having trouble thinking of the best way to tie these repairs - so the customer does not show in the call list until both repairs are completed. Every option I come up with seems to have a drawback or is not very efficient.

Should I just make another column in the repair table called tied_repairs, and include the IDs of all tied repairs separated by a ; (or any character)?

Thanks in advanced.

Improve this question

5 Answers 5

Reset to default
1

Never ever ever put multiple values in one field with a delimiter. This creates all kinds of headaches. How will you search on such a field? How will you do joins? How will the database engine index it? You have to do a whole bunch of string processing. It's bad news.

I see two possible solutions, depending on exactly what the real requirement is.

Secnario 1: If the real requirement is, "A customer may say that they don't want to be called until all pending repairs are complete", then all you need is a field in the customer table that's a boolean, "don't call if any repairs are incomplete". Then the to-call query becomes something like:

select customername, whatever
from customer
join repair rc on rc.customerid=customer.customerid and rc.completed=true and rc.pickedup=false
where customer.allOrNothing=false
or not exists
(select 1 from repair ri where ri.customerid=customer.customerid and ri.completed=false)

Scenario 2: You really do need to tie repairs together, in arbitrary combinations. i.e. a customer may say that they don't want to be called for repair 1 or 3 until both are done, and they don't want to be called for 2 or 5 until both are done, but they could be called for 4 even if none of the others are done, or for 1 and 3, or for 2 and 5. Seems awfully complex for the clerk to enter and maintain the data, but okay.

You'd need a new table, let's call it RepairTies. This table has two columns: RepairTieId and RepairId. Whenever you create such a tie, you create one record in RepairTies for each Repair in the "tied set". You create a RepairTieId to identify the set. The value of the RepairTieId means nothing of itself: it's just something to tie the repair records together -- an "anchor point" if you will. You need this so you can attach all the repair records to something that is not dependent on an individual repair record. (Note you don't want to tie repair #2 to repair #1. What if there are three tied repairs, so you tie #2 and #3 to #1. Then the user says, Oh, wait, #1 shouldn't have been in that list, delete that. Now #2 and #3 tie to ... what? You don't want to lose the fact that #2 and #3 are connected just because #1 went away.)

Then the query becomes:

select customername, whatever
from customer
join repair rc on rc.customerid=customer.customerid and rc.completed=true and rc.pickedup=false
where not exists
(select 1 from repairtie t1
join repairtie t2 on t1.repairtieid=t2.repairtieid and t2.completed=false
where t1.repairid=rc.repairid)

Usual disclaimer: I haven't tested any of this, it's all off the top of my head, so there may be errors in the details, but the principle should work.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, I went with your scenario two. I know putting multiple values in one column is a no-no. A coworker does this and I have always disliked the idea, it gives me a headache when working with those tables/columns. thanks!
1

We have solved this same problem in our application.

We also added another column, but including IDs seperated by a character is a bad idea for a database. We rather did this:

We had a table repairs with example important columns:

  • REPAIR_ID
  • CUSTOMER_ID
  • REPAIR_STATUS (finished, busy, or a percentage, or a date...)

and we added a third column REPAIR_TIE

Now, repairs that are tied all have the same number for REPAIR_TIE, which will be the lowest REPAID_ID.

We can now easily

  • tie repairs to eachother (update all REPAIR_TIE to the lowest REPAIR_ID)
  • untie repairs (set the REPAIR_TIE back to its own REPAIR_ID)
  • check if all repairs with the same REPAIR_TIE have the same status (or get the minimum status, or the numbers of days of work still estimated, or ...)
Improve this answer

3 Comments

Great! I knew I was missing something - and the ; where not a great idea for the database. Appreciate your response!
Now as others will suggest: if you have more information, you can really make a third table repair_ties. The idea is the same: rows in repairs with the same values ... etc. But you can 'group' fields with the same values in the repair_ties (or orders or whatever) table.
I used the solution from scenario two in the other post - going with two tables worked out better in my situation but I do appreciate your help
1

You could create a new table with a one to many association to group repairs by order.

enter image description here

Improve this answer

Comments

1

Whenever a repair is completed, do a select statement on the repairs table for a CustomerID where RepairTable.Completed = False. If there are no results, add the record to the CallList table. If there are results, then the repairman knows the customer has another open repair. The only way this route will get tricky is if the customer cancels a repair, in which case you'll have to run the recent completed repairs against the CallList table to see if there were any that weren't called on (maybe a Returned field could simplify this).

Improve this answer

Comments

1

the correct answer depends on your.

an better approach is

create intermedium entity called "order = {id, customer, status, advanced, savedby, editby, cancelledby, requestdate, cancelldate, updatedate }

" and "orderdetail = {id, order, orderdetails, attendby, status_service, status, dateattended, note} "

or use that NOT recommend

the logic is very easy

added column in repais that called ordernumber

added column in repais that called servicestatus

added column in repais that called notshowagain

so for find clients with order completed

select bla, bla
from customer c

inner join 
(select customerid, ordernumber, count(*) services
from repairs
where repairs.status = {1 | 'A' | your status for entity}
group by customerid, ordernumber
) order
on order.customerid = c.id


left join 
(select customerid, ordernumber, count(*) servicesdone
from repairs
where repairs.status = {1 | 'A' | your status for entity}
and servicestatus = 'COMPLETED'
group by customerid, ordernumber
) orderdone
on orderdone.customerid = order.customerid
and orderdone.ordernumber= order.ordernumber

where  order.services = orderdone.servicesdone

-- is you have intermediary table "order" you can filter by status, or date of request -- percentage of advanced, by attende by, o show notes by some what technical found (in notes)

now you can

1.- execute sql and mark the register how "notshowagain"

2.- do user mark that ("notshowagain")

i prefer the second

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.