Pricelist database design

[expired user #9545]'s profile image [expired user #9545] posted 8 years ago in General Permalink

Hi All,

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

Question 1

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.

lukinhaspm's profile image lukinhaspm posted 8 years ago Permalink

Hello hyungsup2,

I don't know if this forum is the best place to ask about how to build tables or structures, however, if you would like, call me in the Skype - dj_lukinhas, and I can try to help you. I have some solutions if you need.

Regards

hiteshchauhan10's profile image hiteshchauhan10 posted 5 years ago Permalink

I found this[ bootstrap 4 list drag and drop list example] to show MySQL database values

Please login to leave a reply, or register at first.