Hey folks,
I'm trying to build a better system to manage customer returns. In the Excel table, all of the data for one record is kept in one row. One return is an RMA which has a unique RMA Number, and contains many parts, which have part numbers and serial numbers (in our application, referred to as date codes).
In the DB, I split this into two major tables; RMAs and Parts Returned. I use RMA Number as the primary key because they are the unique identifier, and I prefer to have meaningful PK when possible rather than auto-number when it should be strictly enforced. I attached my table relationship diagram for more details.
My main question is, how would I go about taking data imported from the Excel and appending it to each of these tables? Can I write a single query that will first append relevant columns to the RMA table, and then append the other relevant columns to the Parts Returned table, and somehow match the foreign key on the Parts Returned table back to the RMA number of the RMA Table? I haven't touched SQL in a few years but am going to dive back in with this; just curious if I have the right approach.
Hope what I'm saying is clear enough; if not I'm happy to try to clarify and provide more info.
RMA Database Diagram.pdf