we maintain all the available IP address of a single table.
we maintain the virtual machine info in another table , but specific IP address can be assigned to a single VM at any time.
so i have added a drop down list when adding a VM which contain all the IP address that have their status as “available”.
but since two users can create a VM and they might select the same available IP address
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.
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.
Member
488 Points
2558 Posts
Forcing a field uniqueness on the database is it a bad design
May 31, 2013 09:12 PM|johnjohn123123|LINK
I have the following tables.
Available_Ip_Address (IP_Address_ID (Primary Key ), description, status ).
Virtual_Machine (VM_ID (Primary Key ), description, IP_Address (foreign key ) )
The scenario is as follow:-
we maintain all the available IP address of a single table.
we maintain the virtual machine info in another table , but specific IP address can be assigned to a single VM at any time.
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>Contributor
4113 Points
1349 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.