Forcing a field uniqueness on the database is it a bad design [Answered]RSS

• • Reply
  

  johnjohn1231...

  Member

  476 Points

  2453 Posts

  Forcing a field uniqueness on the database is it a bad design

  May 31, 2013 09:12 PM|johnjohn123123|LINK

  <div class="post-text" itemprop="description">

  I have the following tables.

  1. Available_Ip_Address (IP_Address_ID (Primary Key ), description, status ).

  2. Virtual_Machine (VM_ID (Primary Key ), description, IP_Address (foreign key ) )

  The scenario is as follow:-

  1. we maintain all the available IP address of a single table.

  2. we maintain the virtual machine info in another table , but specific IP address can be assigned to a single VM at any time.

  3. so i have added a drop down list when adding a VM which contain all the IP address that have their status as “available”.
  4. but since two users can create a VM and they might select the same available IP address
  5. so i found that the best way to protect our system is to make the foreign key IP_Address in the Virtual_Machine table unique.
  6. then if two users select the same IP address an exception will be raised in the Database for one of them and i can propagate the exception and then display a user friendly message to the user that the IP address might have being already taken.

  So is my approach considered a valid design, or i should look for adding a timestamp field on the IP_address field so if the status of the IP address was changed since it was last queried then the record will have new timestamp and a exception will be raised on the application level.

  Best Regards

  </div>
• • Reply
  

  stmarti

  Contributor

  4113 Points

  1355 Posts

  Re: Forcing a field uniqueness on the database is it a bad design

  Jun 01, 2013 02:30 AM|stmarti|LINK

  Nothing worng with your design.