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)

dognose
  • 20,360
  • 9
  • 61
  • 107
  • 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. – tadman Dec 11 '17 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, ...)` – dognose Dec 11 '17 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"... – dognose Dec 11 '17 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. – tadman Dec 11 '17 at 17:19
  • Save yourself a world of pain and just store the type separately. – Strawberry Dec 11 '17 at 17:22
  • Don't get me wrong, a transactional insert would perfectly work out and is no big deal. Just curious if there is "something else", cause inserts like that are very common for "bussiness applications". – dognose Dec 11 '17 at 17:23
  • @Strawberry I don't see how seperating the "Type" from the main-table would offer anything to solve the problem? – dognose Dec 11 '17 at 17:31
  • Don't worry about the ids. The value stored as part of the database internals need have no bearing on the generation of any kind of public key, but at 14k, I guess you know that already !?! – Strawberry Dec 11 '17 at 17:36
  • @Strawberry I'm not sure what you mean about "14k"? But ofc. I know that the Database-surrogate-key got nothing to do with the final representation for the "User". (It just has to be bidirectional to ensure uniqueness, when accessing a record) – dognose Dec 11 '17 at 17:44

0 Answers0