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?

What I tried

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

    Question 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.
    It's just about what I would do. I think there are various opinions.

    Question 1

    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.
    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)

    Question 2

    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.