SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot impunjabians's Avatar
    Join Date
    Dec 2007
    Location
    Bed Room
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Autonumber format problem

    I'm creating a MySQL database, what i want is that in employee table EmpId's value of EmpId which is autonumber starts from 1 .... , but i need it to start from 40010.......

    thanks

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    ALTER TABLE `employee` AUTO_INCREMENT = 40010
    http://dev.mysql.com/doc/refman/5.0/...increment.html

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how to create table with autoincrement
    http://dev.mysql.com/doc/refman/5.0/...increment.html

    as for starting value, use syntax
    ALTER TABLE <table name> AUTO_INCREMENT = 40010

  4. #4
    SitePoint Zealot impunjabians's Avatar
    Join Date
    Dec 2007
    Location
    Bed Room
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for posting,

    consider the scenario, in a company there are 4 departments,
    Marketing,
    Accounts,
    Procurement
    Workers

    each department employees over 100 employees.
    If we want to append their respective department initial before their ID

    e.g.

    for an Accountant with ID 40010, we want it to store in database as A40010
    i tried following query but didn't worked

    ALTER TABLE `employee` AUTO_INCREMENT = 'A40010'
    what is right way to solve this problem ?

    thanks for reading

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use different filed to store departmen code, then on SQL you can concatenate department code and id

    like
    select depCode || id from `employees`

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    As above.

    If an employee changes department, your method would cause you lots of problems, as you'd have to update every table that used the employee id as a foreign key.

    Using two separate fields means your employee can change departments as often as he/she likes, but the unique id stays the same, only the department field needs to be changed.

    Overloading a field by having two or more meanings encoded in the one value is usually a bad idea.

  7. #7
    SitePoint Zealot impunjabians's Avatar
    Join Date
    Dec 2007
    Location
    Bed Room
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmmmmm? Dr. Thanks for posting, Let's suppose employee department changing scenario, where at first employee A40010 is working in Accounts Department, his actions are recorded in database along his Identification. When employee changes department from Accounts to Marketing.

    Do we need to terminate him first from Accounts department so that he can re-join company in marketing department ?

    If we terminate his employment from company, then there are other obligations like tax and insurance plans.. etc

    Should we develop a mechanism to update his employment status ?

    If we update the status of employee by just changing the employment department which will automatically update the employee, It will also allot a new id with M40010.



    Do we need to update his previous action's ID (was used as foreign key in actions table)?

    in my conviction we don't need because if an employee has switched from Accounts department to Marketing department, after taking charge as an marketing officer his operations will be recorded as operations of other marketing department officers are done.

    Thanks

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This suggestion to have a separate column for the department, which is the right way to do this, is exclusive of your idea of putting a letter on the front of the ID. Stop thinking about that, it's just a number, plus a separate department column.

    To change the employee's department, you UPDATE the one row in the employee table with the new value for the department column. All the data associated with that employee is still associated with that employee because it's referencing his employee ID which has not changed.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •