Part Number Generation in MySQL

[Ian Lance]

Hi, I'm trying to make a Part Number (PN) generator for my company using MySQL.

Originally, I made this tool in Excel. It works great, but it's starting to slow down hard from the significant amount of data being processed.
I believe that a MySQL database will be a safer and more efficient way of generating and storing this information.


Now that that's out of the way, let me explain the components and intended result.


Table 1: Compounds

Table 1 defines the material that our product is made from. Example compound code: KP75


Table 2: Sizes

Table 2 defines the size code for the product. Example size code: -2x5


Table 3: Part Numbers

We technically have two PNs: Semi-numeric code ("computer PN") and a Customer Facing code ("actual PN")

Example Computer PN: AS1000034
Example Actual PN: KP75-2X5


Currently, Table 1 and Table 2 are finished, I'm just trying to figure out how to approach Table 3. I'm thinking about using a view, but I'm not sure if that makes sense considering how necessary it is for both the Computer PN and Actual PN to remain constant.


Additional Notes:

- The Computer PN is incremental.
- There are currently around 130 compounds and 2500 different sizes. That would mean that, ideally, when I add a new material to Table 1, 2500 new PNs would automatically be created in Table 3.


Sorry if any of this is a bit unclear - I'm still very new to MySQL.