I would like to manage the production of the apparel manufacturing industry at ODM.
Specifically, we would like to calculate the achievement rate for the quotation for each order.
The only tool I can use is Excel and I want to calculate with powerpivot or SQL.
Currently, there is a quotation and order list based on the company's product number (omitted).
We often use order IDs and product numbers within the company.
Two customer product numbers may occur due to material changes, etc. for one company product number.
Product number 1 Company A product number 111-1
Product number 1 Company A product number 111-2
(Currently, the product number is less than 2000, and about 10 of them have multiple customer product numbers.)
In addition, the estimate may change due to material price increases, etc.
Product number 1 Until 2019/12/31 Price 20000 yen
Product number 1 from January 1, 2020 Price 23000 yen
(Estimate changes are about 0 to 20 and vary depending on the year)
In such a case, how should I make a table?
Question 1. I thought about assigning my own product number to the customer's product number
Separate the product table and the product quotation table
How about versioning a quote?
Then, the product quotation is not linked to the customer part number table,
I think that the price will be decided by fixing the order date in the order list during the product quotation period.
I think that's very true.
Question 2. Having unit prices, sales, and product numbers in the order table adds redundancy,
Do you think it's okay to have one?
I think that it is a beginner and the hurdle is high, but
If there is a good way, I hope you can teach me.
Answer # 1
Quotations and orders should be related to the customer.
Normally, the order is quote → order, but in the field, there are many orders without quotation.
In particular, the quote should not be a product relation.
Two customer product numbers are generated due to material change etc. in the product number
This is because the act of "changing the material to the product number, etc." itself is an estimate, and that gives rise to the product number.
Thinking of the entities in chronological order of occurrence makes it easier to design relationships.Question 2.
I don't think it's redundant because it's better to keep the estimated price and the order price separate.
Answer # 2
I think it's better to ask separately because the content is quite complicated, but I will answer for the time being.Question 1
It's just about what I would do. I think there are various opinions.
M I wouldn't make a customer part number table if it's just a visible field.
T Fields such as [Product number] and [Product name] are provided in the order table for each order.
In addition to entering the product number, I think you should also fill in the [Product number] and [Product name].
In the M product quotation table, PK is used as the primary key in one column such as the quotation ID.Question 2
T You can enter the quotation ID etc. in the order table and pull the unit price etc. from the quotation
Allows you to fill in product-related information.
When entering the quotation ID, you can assist by looking at the period such as the date and listing it.
(I personally don't think the quote will be a master table)
There is no option to omit the unit price or product number in the order table.
(I don't know what the sales are. For example, if you have a T sales table, you don't need it.)
Personally, the master table is used to assist input.
If it is necessary to keep the value, it should be on the order table side.
I don't know what the UI looks like if I try to solve everything with just concatenation
You need to look at the tables here and there, and the person in charge of input also needs it.
When it comes to the master table, there is a possibility of mistouching items that should not be changed.
It depends on how the questioner thinks about "orders".
I think that "orders" are "things that can be registered with the contents written on the purchase order".
T I think you need a container of data that can reproduce the customer's purchase order in the order table.
- mongodb - about the database design of the two-choice question app
- django - about database design
- mysql - about database design and implementation
- mysql - about database design
- about dynamic table design policy in jsp-java servlet-db linkage
- database - about the advantages and disadvantages of db column division
- css3 - about classes and web design in html5&css
- mysql - about the database
- ruby on rails - about the legitimacy of db design
- java - about the design pattern iterator
- about setting up the oracle database
- sqlite - please tell me about foreign keys in database orm
- reactjs - about the design when creating a web application with firebase
- vba - [database design] about table design when referencing/updating the access database using excel as an input screen
- mysql - about database structure and how to hold data
- about design when developing games in c ++
- php - about the prepare method of the database handler
- oracle database - about recovery of flashback database
- about database operation in a branch other than the git master branch
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- i want to call a child component method from a parent in vuejs
- python 3x - typeerror: 'method' object is not subscriptable
- dart - flutter: the instance member'stars' can't be accessed in an initializer error
- xcode - pod install [!] no `podfile 'found in the project directory