0

Im working on a project, where I need to insert various "Products", BUT depending on the type (fixed amount of 3, won't change customer said) the "ID" should start with a certain number (2,3,4)

While my first-thought was to simply use a (in the code removed) Prefix, then the id padded with "0" - this won't work, because an import from an "old" System should be performed, where 3 Tables (rahter than one) have been used.

So, the old system will deliver (from 3 tables) IDs like 

200001 | Raw-Material-Table
200002 | Raw-Material-Table
300001 | Composite-Table
300002 | Composite-Table
400001 | Product-Table
400002 | Product-Table

and so on. (it was a "lazy" design, the table where a product was stored was telling apart one of 3 states a product could have - that has been simpliefied to a single column now - all other columns are equal (base-attributes), additional attributes are EAV)

Therefore, the mixed data-set of 3 tables should be inserted into a single table, while maintaining the first "digit" (2,3,4) for future inserts.

So, I would need some sort of "insert" (causing surrogate IDs according to auto-increment), but also create the artificial ID based on the "State".

What options are available for such a scenario? Preferably "less code" and "more query" (if possible at all without code)

Improve this question
9
  • Use your AUTO_INCREMENT identifier as a simple "row ID" and then create your own custom "product identifier" with whatever spec you need, including letters or whatever. Make that a UNIQUE constraint. You can always create a sequence generator for the identifier values. Commented Dec 11, 2017 at 17:00
  • There is no "sequence generator" in MYsql... (theres only auto-increment) - ofc. I could use three tables only for id generation, then run the insert inside a transcation - but the problem seems pretty common to me, so maybe there is "better" solution - like INSERT INTO products, id_table_1 ... (products.id, ...) VALUES (id_table_1.LAST_INSERT_ID, ...) Commented Dec 11, 2017 at 17:10
  • ps.: A column that is filled by a trigger would work, but i'm not a friend of "distributed logic" - Database is for data, Filesystem for files, Code for "code"... Commented Dec 11, 2017 at 17:17
  • There's no built-in sequence generator, but that concept can be implemented. Using other tables as sequence generators could work, but tends to be kind of messy. An optimistic system using a transaction is actually not that hard to write, and with a UNIQUE constraint you're protected from inadvertent duplicates. Commented Dec 11, 2017 at 17:19
  • Save yourself a world of pain and just store the type separately. Commented Dec 11, 2017 at 17:22
Related questions
4
Ambiguous `id` Field
11
Naming the MYSQL id column
1
MySQL "non unique id"
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.