In creating your database you must set in mind that it should always complies with normalization.
If you are given a task to create unique IDs for every person who register on your website (e.g. REG-2011-000001) you must find a better way to represent the data on the front end and back end properly.
If you will visit forums some of the idea maybe given to you is to create random numbers, or a row counter for the value of your IDs.
- Generating random numbers for you IDs is prone for data duplication since a random number is not always unique every time it was created, The solution for this is a 'checker for duplicate IDs' but it has drawbacks on performance since every time you need to add a new row, you need to query against your database for checking.
- A row counter is prone for data inconsistency. If you don't have a 'place holder for the number of deleted rows', you will need this as an 'adder variable' to the current row count. The performance also pay the price since you do query to count rows before adding a new row.
You must do it plain and simple, Let's begin!...
1.) Open your MySQL monitory and execute the commands on the figure below.
On this Registration table we will use the id column and created column to make it look like REG-2011-000001 upon data representation, Both id column and created column is not required on insert statement.
2.) Code the php script, follow the code below.
On this code the EXTRACT and the PRINTF function is used to represent the IDs properly with formatting.
2.) Output should look like below.
