Could use some help on table (relationships) design

[88scythe]

I'm working on a database that tracks the handling of our RMA's.
This is how the handling process could go:

-1- Customer calls or emails our support department, with one or more defective devices, each having their own serial number. Our support department creates an RMA (tblRMA), and adds the products to that RMA (tblHandlingSupport).

-2- Depending on where the customer is located, he can either:

-2a- send the devices directly to our headquarters, so it gets repaired and gets shipped back, or in case of unrepairable: a replacement product gets sent back (tblHandlingHQ).

-2b- send the device to one of our "swapstocks", which sends the customer a new spare device (tblHandlingSwapstock), and forwards the defective device to the headquarters (tblHandlingSwapstock.) This defective device then gets repaired in our HQ, and it gets sent back (or a replacement in case of unrepairable) to the swapstock, so they can use it for a next case. (tblHandlingHQ)

Let's now pick option 2b, so the customer will send his device to one of our swapstocks nearby.

I have created a table for the handling by the support department, containing the serial number of the defective product as mentioned by the customer [HSpSerial]

If everything goes well, that is also the serial number that the swapstock receives from the customer. However, the customer sometimes sends the wrong device (other serial) back. I track this by means of the field [HSSReceivedFromCustomer].

The swapstock provides the customer with one of their spare devices, and enters the serial in [HSSReadyForCustomer]

The swapstock forwards the defective device to the HQ. It doesn't happen often, but sometimes, also here, an other serial has been received than the one that was expected. I track this by means of the field [HHQReceivedInHQ)]

The serial number of the repaired or replacement device that gets sent (either to the customer -2a-, or to the swapstock -2b-), gets entered in the field [HHQSentFromHQ]

The swapstock enters the new / repaired serial that they have received from the HQ in [HSSReceivedFromHQ] -should be the same as [HHQSentFromHQ], but you never know ...

I would now to put the serials in their own database, and link tblSerials to:

- tblHandlingSupport: no issues here.

- tblBlacklistDevices: an extra table, for serials that have been reported as stolen / ... - no issues here

- tblHandlingSwapstock: because the swapstock receives a serial from the customer, sends a serial to the customer, and receives a serial from our HQ, I'd need three relationships between those two tables. I'm not sure what's "best practice" for this kind of thing.

- tblHandlingHQ: also here, I'd need multiple relationships between the two tables: HQ receives a serial, and sends a serial. I'm not sure what's "best practice" for this kind of thing.

How do I solve this issue?
Do I use multiple instances of tblSerials? Or is there a better way?

Any input is appreciated!

I have added a sample database (left out the tables that were not relevant to this post) and a picture with the relationships I'd need.

 

[jdraw]

Step back a little and overview WHAT is the issue. What's an RMA? What is your company?

Pretend we have no idea of your business or any of its associate "jargon" terminology.
What would you tell someone in 5 sentences or less about you, your issue, its implications?

 

[88scythe]

I tried to limit the jargon and tried to explain everything as clear as possible. I had hoped that the description, together with the added sample database and image as attachment would give a clear overview; but to answer your questions:

- The company sells electronic devices (would rather not go into detail, as this is not needed.)

- In a lot of companies, an RMA is when the customer sends back a defective device to the company, and then gets the device (repaired) back, or gets a new device (when the defective can't be repaired) back.

We are now tracking the RMA process (serial numbers, dates, types of defects ...) in an ancient Access Database, I want to create a new database from scratch because the old one has become almost unusable.

The problem I'm facing is clearly described in the original post: right now, I'd like to have a table containing serial numbers (in the database we use now, serials are simply manually entered and stored in a text field.)

However, in two other tables, I'd need multiple fields that contain serial numbers (e.g. incoming serial, outgoing serial)

My question is: do I use multiple instances of the Serials table to link the serials to those fields, or is this not the preferred approach for such situations?

 

[jdraw]

Why did the old database become unusable? How would that be prevented with a new database?

I have listed some facts based on your post. Perhaps you could confirm them or revise them as required.(You know the business and we don't)


Deduced Business Rules:

Our Company has many Customers
Our Company has a HQ and >1 SwapStocks
A Customer may have 1 or more defective devices.
Each device has a unique SerialNo
HQ deals with Customers in certain Locations
Each SwapStocks deals with Customers in certain Locations
Each defective device resolution is initiated using a RMA
The SerialNo of the defective device is recorded in the RMA
A defective device resolution may be a repair or a replacement
Each repair is done by HQ
A defective device may be sent to HQ
A defective device may be deemed unrepairable
An unrepairable device is replaced with a replacement device
A defective device may be sent to a SwapStocks
If a defective device is sent to SwapStocks, it is forwarded to HQ and SwapStocks sends a replacement device to the Customer
If SwapStocks sends a defective device to HQ, it may be repaired and returned to SwapStocks
If a defective device is sent to HQ, HQ may send a replacement device to the Customer
Sometimes the defective device SerialNo is not the same as recorded on the RMA
Our Company maintains a list of Stolen/Blacklisted SerialNo


What paperwork/mechanism is associated with the physical defective device sent for repair? That is what ties an RMA with a physical device?
What happens to an unrepairable device and associated information (SerialNo, any history..)???
As I read your description, everything seems to revolve around RMA info and actual SerialNos.

 

[88scythe]

Hi Jdraw

The old database lacked about each and every principle of a "good" database. It's too long a story to explain, and not really the point here. The old thing was just plain worthless to develop any further. It would have taken ages to clean up before it could be expanded.

I also appreciate your input, thought work and deduced business rules, but the part I have talked about in this thread is only a small bit of the database / process flow.
In the full database, I have a lot of tables and relationships extra, and I've already spent quite a bit of time developing it. I've read about database normalising, and think I've done pretty well.

The only issue I'm still facing is what I have explained in my first post. Did you check the image I have provided in the attachment? I think that makes clear what I want to ask.

I had originally planned to store serial numbers in text fields in other tables, but I'd prefer now to store serial numbers in their own table.

The problem now is: do I use multiple instances of the Serials table to create multiple relationships between Serials and an other table, or is there a better way to handle the necessity of multiple relationships between two tables?

 

[jdraw]

I have always found that looking at the big picture in the conceptual design stage was helpful. You can logically design pieces without physically building them, and still be aware of where and why they "fit" where they do. You can always stick the big picture up on the wall for all to see- make it the center for communications, planning new features, scheduling development, performance improvement and training others in the database design.

You really have to test cases based on your business rules to see that the model actually supports what you are trying to do. If you start by identifying what each test should do, and compare it with what each case actually does, and reconcile every discrepancy you will have confirmed your design.

Another thing that may be causing issues or making some design decisions difficult is getting too involved with How as compared to WHAT. The database is about the What (the Entities/Subjects and attributes and the relationships ), it is user interface and logic and forms that deal with HOW we interact with that database.

You can use the same table multiple times in a database. For example, a Personnel table could be used to populate Managers, Engineers and Admin staff, Security workers and part time people -- same table, just slightly different contexts.

Anyway, it seems you know what you have and what you need, so good luck with your project.

 

[88scythe]

I appreciate the time you took to reply, jdraw.

Just posting this for people stumbling on this thread via Google. It's as close as I got to an actual answer.
Can't post the link to the source as I need a post count above 10 to be able to post links. It's from the office-archive.com forums though.

You need multiple copies of the same table in the Relationships window when you have multple independent joins between 2 tables.
(...)
Example where it is useful:
Table of machines with these fields:
MachineID primary key for this machine
OperatorID the staff person who uses the machine
RepairerID the staff person who fixes the machine.
That needs two relationships between the machine table and the staff table,
so you would need a second copy of the staff table in the staff table in the
relationships window. (That's different from a relationship based on a pair
of fields.)
--
Allen Browne, Microsoft MVP (Most Valuable Professional)
Perth, Western Australia

So I guess it's OK to do in my case (it resembles the situation above.)

Topic can be closed for me.