I am not sure of the best way to explain this problem, so please bear with me…

I have inherited a database which is not normalised and has poor data integrity.

The business repairs car parts which are booked in to the workshop, then assessed, then fixed, scrapped or sent away for repair. A key problem is that there is no one table for the parts - the ASSETS table - containing the part's serial number etc. They appear in several different tables. I need to rectify this and I know the table schema that I want.

My problem is how to implement the booking in process so that the database 'looks' in the ASSETS table to see if the part already exists and if it doesn't, create it. If it does already exist, don't create a duplicate. In either case, a BOOKING IN record needs to be created to store date/time info for this event.

Do I record BOOKING info and ASSET info in a temp table, then separate them into two tables when the booking is complete? Do I record all the info in the BOOKING table then remove the ASSET info to its own table?

Sounds a simple problem - it's essentially the same problem as having a sales database which stores customer information in a CUSTOMERS table and then adds distinct new customers when they order for the first time. I'm sure there must be many solutions out there but I can't find them! Maybe someone could point me in the right direction….

Thanks for your help everyone 😀