Our new project is to write a customer order management system. My specific task is to import customer orders from various different sources - XML, CSV, XLS... etc.
Our old DBA has left the company and I have stepped up to the plate. I have plenty of SQL experience but for the first time I'm required to design a brand new system with a feasible data structure to hold the orders of many different customers, which we import into the database. I don't want to have to change the database every time we have a new customer order with a slightly different structure.
The problem is that every customer wants to provide us with an output from their computer system, and every customer's orders are different. Our plan is to write a script for each unique customer to take their data file and transform it into our orders table.
So far so good, but what will my customer orders table look like? Every customer is going to have their own unique fields, some will be mappable to generic fields (like order number, delivery date) for example.. but if I'm to build a solid foundation, I need ideas to store this data in a flexible and scalable way.
Does anyone have any experience with this kind of thing? Any ideas or tips would be very much appreciated. Thanks for your time.