Best Method Question

[jlego]

the language is vb.net, but it is more of a mysql database question.

I have a table that holds customer repair information (where it is, whats going on, if it was completed, etc).

the table starts with a basic auto increment ID, the id is used to view the repair information associate with a customer_id (from customer table, not relative)

lets say a customer has two repairs in the system, and wants them put together and only called when both are complete.

what is the best method to link these repairs? (keep in mind he could have anywhere from say 1-10 repairs that they might want linked together)

example:
repair_id linked_to
1.001 2.001
2.001 1.001

should i create a seperate table that links the repair ids, or create a column called linked that holds the repair ids that it is linked to?

example:
linked_repairs
1.001, 2.001, 3.001

i am not exactly sure the best method to go about this, would like some input

thanks

[smantscheff]

You have the customer_id. This is link enough. You can select all repairs for one customer using the customer id, and for formatting purposes you can use the group_concat() function, like in

select group_concat(repair_id) from repairs where customer_id=$fixed_value;

[jlego]

smants - i understand what you mean perfectly,
but the problem is, the customer may or may not want all their repairs to be completed before they are contacted. they could also have the choose to lets say

i want my two watch repairs completed together, but you can call me anytime for my ring repair

do you see what i mean? i use the customer id to show all repairs for a customer, but i dont think that method will work for linked repairs

[smantscheff]

Assuming completed is a boolean field you can select the boolean and of all completed fields for one customer like that:

select (sum(if(completed,1,0)) - count(*)) as all_completed from repairs where customer_id=$fixed_value

If all_completed is 0, all repair has been done.
For further discussion please provide the CREATE TABLE statemens of the involved tables.

[jlego]

yeah but i dont want it to be mandatory - i want the associates to be able to choose if a repair is linked to another repair, per the customers request.

like, say i have a form that loads a list view. the list view contains all repairs for a customers ID that are not completed. each list view item has a check box, and there is a button that says link checked repairs. Clicking this stops all repairs from going to the contact customer status until all the linked repairs are completed, then they all go to the contact customer area.

but it has to be by choice.. maybe i can get permission to just do it for all of them regardless ill have to find out

[smantscheff]

A clean solution would require two repair tables: repairs and repair_items. A repair is completed if all of its items are completed. Then it's the user's choice to assign an item to a repair.
Again: show the CREATE TABLE statements.