i am using Fat Free Framework to create my database driven application. i create this to easily store my test result based on the ID. the ID is a combination of letter and number like A01 to A100 then B01 to B100 and so on. Right now, i am on the letter H.
each of the ID is a unique column so no two ID can be the same. I am retrieving all data related to the ID like this http://localhost/monitor/A01.
now my problem is, when this year (2017) come to and end, the ID will be reverted back to A01 for 2018 data. my table structure is as follows
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| monitorId | int(10) unsigned | NO | PRI | NULL | auto_increment |
| monitor_label | char(5) | NO | UNI | NULL | |
| casting_date | date | YES | | NULL | |
| casting_time | time | YES | | NULL | |
| clientId | int(10) unsigned | NO | MUL | NULL | |
| projectId | int(10) unsigned | NO | MUL | NULL | |
| gid | int(10) unsigned | NO | MUL | NULL | |
| mid | int(10) unsigned | NO | MUL | NULL | |
| techId | int(10) unsigned | NO | MUL | NULL | |
| cube_size | int(1) unsigned | NO | MUL | NULL | |
| remark | varchar(255) | YES | | NULL | |
| slump | int(3) | NO | | NULL | |
| sample | int(11) | NO | | NULL | |
| data_status | int(10) unsigned | NO | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
the A01 data is stored in monitor_label
You forgot to express your question, but I believe you're wondering how to structure your database.
Well I see two solutions:
1) either you handle the series uniqueness in PHP and thus you just need to replace the unique index on monitor_label by a non-unique one
2) or you store a new monitor_year column in the database and replace the unique index on monitor_label by a composite unique index on monitor_year,monitory_label