Complex Relational Database Problem

[guyfromfl]

I am working on my company's service department software I have been working on for about a month.

 

I have a question for the experts....

 

Table structure: ReturnAuthorization (contains all the who, and when...)

ReturnAuthorizationItems (contains all the what products came back and what was done)

 

 

Basically, I am creating the return authorization for warranty repair part of the program.  Lets say you buy 3 of our products, but they are broken out of the box, you send it to our repair station with RA #1000.  We repair the items, but one is still not fixed so you have to send it in again for a second time.  When you send the product back for the second time it is comming in on RA #1200 with a higher priority.

 

That part is not really a big deal.

 

Where I am going with this, is what if RA #1200 isn't fixed, and it comes back on RA #1400.  Now, I have to make the software recognize RA#1400 came back on RA#1200 that came back on #1000, so the tech can easily pull those records to see what was already done.  The other 2 items on #1000 are ok, so they are ignored.

 

When the tech pulls up RA #1400, he can see that particular item has been in on #1200 and #1000 before that.

 

So basically how do I relate multiple children tables of a specific parent table to another new parent table.

 

I really don't even know how to put that in words, hopefully you get the idea.

 

Would it be best to pull the passed RAs from the items table to an RA in my software, then create individual queries or is there a way to query that data all on the DB?

 

I really hope someone understands all that!  Thanks in advance for the help

 

 

[kickstart]

Hi

 

Can you not just do a JOIN of the ReturnAuthorizationItems against itself where the item id is the same. This should bring back one line for each return for each item.

 

All the best

 

Keith

[guyfromfl]

I don't know..

 

Everything is related to the customer, a parts order, ra, issues etc.  You find the customer, then open the return authorization.

 

So I need to find all the times his specific product has been to the shop and all the passed RAs that it came in as.

 

My SQL class didnt really get into many:many relationships like this.

 

I could make a previousRepair field in the RAItems table and query until that field is null, or build an array, but there has to be a better way.

[kickstart]

Hi

 

Can you give the relevant bits of the table layouts and maybe a couple of example rows.

 

All the best

 

Keith

[guyfromfl]

Yea its kind of complex, these are the actual table names and some of the fields:

 

repair:

repairId (primary key)

custId (relates to the the customer ID in the customer table..)

 

repairItem:  (Has many entries per repairId .. iow #RA1000 can have alot of engines to be returned on.)

repairItemId (primary key)

repairId (relates to repair.repairId)

 

 

So the tables would look like this according to my 1st example:

 

repair:

repairId    custId

1000            1033

1200            1033

1400            1033

 

repairItems:

repairItemId repairId stockNum

1                1000      5011

2                1000      2020

3                1000      5075

4                1200      5011

5                1400      5011

 

 

So your eye can see that 5011 came in on 1400, 1200 and 1000, but how do you make the computer know that?

 

Now when the user opens RA1400, how do I display a table with links to the other 2 RAs?  I can't use WHERE stockNum=5011 because 5011 isn't specific to that repair since we made say 300 products with the stock number 5011...

 

I hope that helped clearify it, please ask if not

[kickstart]

How do you identify (in English) that the stock number refers to the same actual item?

 

All the best

 

Keith

[nadeemshafi9]

So basically how do I relate multiple children tables of a specific parent table to another new parent table.

 

OK,

 

so if your creating new records starting from now on,

 

when an order comes in just relate it to the parent table as normal and create a new relation between the other parent, you can disregard all the old system data.

 

OR

 

you can do a query to build new relations for each of teh chiclderen of the parent to a new parent

 

then you can vew teh old system data on your new system

 

you may need new xref tables for many to manys or add new feild to teh other parent

 

you can fill it in using sql or let it get filled with teh new system in place

 

 

 

 

OH and

 

SELECT * FROM repairItems where stockNum = '5011';

 

will return all history for that stock no.

 

SO now you have the stock no items but you say multiple items have the same stock no,

 

 

so where is your unique identifier for the stock item ????????????????????????

 

 

YOU DONT WANT TO KEEP HISTORY OF THE ITEM,

 

 

U RATHER KEEP HISTORY Of CUSTOMER

 

use the name feild for user of app to identofy the item and then select it in the interface and re manipulate it, basicaly in the system

 

you bring up your customer and then you can see all the repairs he has and then you can just search teh repairs for name or look down the list and user can take best guess at wich one it is.

[nadeemshafi9]

you can get the repai item primary key and use it in your sql

[guyfromfl]

Keith,

 

That relation is exactly what I am trying to figure out.

 

from the table i posted before, how is the best way to say that customer 1033's #5011 has already been repaired 2 other times when it comes in on RA1400

 

I know it is possible that customer 1033 has 3 5011s but for right now I want to keep it simple.

 

I am about to just create a table on each RA that has a list of all 1033's repairs and call it a day.  The deadline is comming up quick

 

 

nadeemshafi9,

I was making the same point with the select * where stockNum=5011...

 

I think the table of all customer 1033s records is gonna be the way to go.

 

[kickstart]

Hi

 

Only thing I can suggest is this:-

 

SELECT custId, stockNum, COUNT(*)
FROM repair a
JOIN repairItems b
ON a.repairId = b.repairId
GROUP BY custId, stockNum

 

However this will give incorrect results in someone buys multiples of a certain item

 

All the best

 

Keith

[nadeemshafi9]

just out of interest what interface are you using to work with mysql databases ?

[gizmola]

In repairItems, add a column named parentRepairId.  When an item is being returned a second time, your app should have a function that the user can activate to indicate this.  Then it's a simple matter of either filling the column with zero (has no previous Repair) or the repairID of the previous repair.  This will allow you to establish the hierarchy of repairs on the item.

[guyfromfl]

gizmola,

That was what I was thinking, but i was wondering if there was a better way.  Is there a way to make MySQL do a while statement? or will that part have to be in my script?

 

kickstart,

thanks for the SQL, I will try it out.

 

I am using PHP.  That way if there is an update to the program, only 1 computer needs the update.

 

We were using Access but everybody wants an updated system so I was told to do whatever to make a better system.  The parts ordering part of the software is a huge hit and has worked for 30days now with no problems. so for a small company this system will be good.

[gizmola]

gizmola,

 

That was what I was thinking, but i was wondering if there was a better way.

 

No, that is a standard design pattern, and works well in most databases.  Oracle is somewhat unique in that it has the very helpful CONNECT BY syntax, but I'm going to assume here that your trees aren't going to typically be very long, so even if you have to issue multiple queries to go up the tree to the ultimate parent, it's not going to be much of a problem.

 

 

[fenway]

This is a very common way to handle hierarchies -- it poses some issues for certain types of queries, but usually, it's ok -- mainly because it's the easiest to maintain.