How to store IDs of different auto-increment based on another variable in DB

Question

I was to verify the ideal way to store in the database an ID of the format:

[type] - [year] - [auto-increment]

wherein the auto-increment field is based on the type.

Here are some examples:

ABC-2016-1
ABC-2016-2
XYZ-2016-1
ABC-2016-3
XYZ-2016-2
AAA-2016-1
AAA-2016-2
AAA-2016-3

as we can see, ABC, XYZ and AAA have different auto-increment IDs that differ based on which came first.

What I thought of the table structure is this:

TABLE my_table (
    id INT UNSIGNED AUTO_INCREMENT UNIQUE PRIMARY KEY,
    type ENUM('ABC', 'XYZ', 'AAA') NOT NULL,
    year INT UNSIGNED NOT NULL,
    order INT UNSIGNED NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

just store the first two, and during saving to DB, I search my_table for all enums and get the maximum ie (CI-style, pseudocode only):

$this->db->select_max('order');
$this->db->where('type', $type);
$order = $this->db->get('my_table');

$data_to_save['order'] = $order + 1;

I am not confident with doing the increment every time, is there a better way to deal with this?

Should I just create multiple tables for my types? Say:

TABLE abc (
    id INT UNSIGNED AUTO_INCREMENT UNIQUE PRIMARY KEY,
    year INT UNSIGNED NOT NULL,
    order INT UNSIGNED NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

TABLE xyz (
    id INT UNSIGNED AUTO_INCREMENT UNIQUE PRIMARY KEY,
    year INT UNSIGNED NOT NULL,
    order INT UNSIGNED NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

TABLE aaa (
    id INT UNSIGNED AUTO_INCREMENT UNIQUE PRIMARY KEY,
    year INT UNSIGNED NOT NULL,
    order INT UNSIGNED NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

you are getting a result set in $order, why are you adding 1 to a result set that is an object? — , Sep 30 '16 at 14:06
What is this used for? Are you tracking the operations on some other table? — DFriend, Sep 30 '16 at 14:08
@ShoyebSheikh: do not mind the values, it is just pseudocode. Please focus on the SQL. — , Sep 30 '16 at 14:15
@DFriend: it is to ID a document of different predefined "kinds". I just used `INSERT`... for confidentiality. — , Sep 30 '16 at 14:15

score 2 · Accepted Answer · answered Sep 30 '16 at 14:32

Following new information, if you want each customer to have a counter that is like an auto increment for that customer, there are a variety of ways to do it but it is not an auto increment from the database.

First do a query to count_all_results for that customer. http://www.codeigniter.com/user_guide/database/query_builder.html#CI_DB_query_builder::count_all_results This will return 0, 1, 2, etc.
Add one to the counter.
Insert new records with this counter value in a seperate field.

The same still applies in terms of creating references on the fly from seperate fields and not re-storing the reference in another column. However if you really wanted to you could, before insert, create the reference format from year and counter values, and store it, but I would not do this.

Another way is to store a counter in the customer table, order_count or similar. When creating a new order, you can then increment the order_count and use that value for the new order record. This has the disadvantage of being prone to get out of sync and thus give the wrong count value, leading to duplicate order values, but has the advantage of showing total orders placed, sorting customers on total orders etc. Personally I would not do this but generate such information on the fly based on the orders in the order table.

Hope that helps,

PS if deleting an order do a soft delete. Ie keep the order for your records but have another column that indicates the order was deleted. — PaulD, Sep 30 '16 at 14:34
Thanks, I already implemented the [versions](http://stackoverflow.com/questions/2015232/database-design-for-audit-logging/2020371#2020371). — , Sep 30 '16 at 14:35

score 1 · Answer 2 · answered Sep 30 '16 at 14:15

1

This seems to make little sense in the approach.

In you db you would insert your record like normal. Then use

$id = $this->db->insert_id();

To get the id of the newly inserted data if you need it.

If you want to set an order use an auto set creation date. If you want to generate a code for the item, why would you want to encode the order and year? If the year is the year it was created, this should be a timestamp field so you can then get items created in january, or on week 4. If the order is fixed you just need to sort on date. If the order is a sort order that can be changed it should be a column on it's own so it can be referred to directly and edited if required.

If this is of no help, it is because it is not entirely clear what you are trying to achieve. The encoded id-year-order type reference is something you generate from all three fields separately, and if looking up, you decode the reference to get the fields you need to look up the item. In fact you only need the id in this case to identify the record being referred to.

answered Sep 30 '16 at 14:15

PaulD

1,161
1
9
14

The order and year are business logic, the enum-year-order is the customer's. I just want to know how can I control the order based on the enum ideally. – Sep 30 '16 at 14:17
You use the ID, that is what it is for. Customer requests 16-2016-3 and you know that 16 is the id, 2016 is the year and 3 is the order, so to delete the record, say, you delete id 16. To create the reference on output you simply create it from the three separate fields. Otherwise you are encoding separate information and storing the same information twice. – PaulD Sep 30 '16 at 14:19
No, I have no intentions for storing 16-2016-3. I honestly just want to know how can I make sure all of the first element in the ID always have their *own* auto-increment. – Sep 30 '16 at 14:22
Sorry, again confused. The table you are storing the records has one column id, which is auto incremented so is always unique. See second answer which I am writing now - give me a minute. – PaulD Sep 30 '16 at 14:24

How to store IDs of different auto-increment based on another variable in DB

2 Answers2