I'm trying to relearn MySQL and I need your help.
I have 2 files that's generally created by my colleagues.
A file that has Pricelist for all the customer ie.
Customer - Model - Price - Currency - Start date - End date
XXX - TED - 112 - GBP - 2015/01/01 - 2015/12/31
XXX - TED - 103 - GBP - 2016/01/01 - NULL
XXX - BED - 123 - EUR - 2016/01/01 - NULL
A file that has an allowance for all the above models
Customer - Model - Support AMT- Currency - Start date - End date
XXX - TED - 10 - GBP - 2016/01/01 - 2016/03/01
XXX - TED - 10 - GBP - 2016/03/02 - 2016/05/01
What is the best way to create the table? At the moment I would like to create 3 tables. 1 table with Customer list table including Customer and Model fields and Primary Key 1 Table for Pricelist Table Linked with customer list table and price, start date and end date. 1 table for Allowance table where it's linked with customer list table and support , start date and end date.
Any other ideas?
Question 2. When importing new data.
Price list. If there is a new price list to be effective in 3rd March 2016(say it's now 18th Jan). In the list only the model TED is included priced @ 89. Before I upload the data into MySQL I would like to update the end date column as 2nd March where the price is 103 (2nd line). This way, when I query the price @ 3rd march, It will return 89 and if I query the price @ 1st March it will return 103 GBP.
I've only started few days ago, so if you believe I need to redo from scratch, I would gladly do so. :)
If you guys can give me any help, tips, or any materials that I can read, I would really appreciate it.