hi dears i am new developer i am creating a database in Visual Studio and database will deal with usernames and IP addresses i have two table (selected) one USERS and other is IP_ADDR USER[ UserID, Name, Address, contact, zone, ip] IP_ADDR [ userID, IP, ZoneNam]
i want to relate them into one to one relation ship the scenario is there is one zone created on given ip range like 18.104.22.168 to22.214.171.124 and stored into IP_Addr register and each user will be created (when a new user will get registerd ) it will be assign a unique IP from the zone and when a user will be disable or deleted the IP of that user must be free so that it can be utilized by some other user now plz tell me how to create this relationship or i am missing something or not doing good i am confused with making PK on both tables userid is as PK and in second table i set PK both IP and Zonename am i right plz guide me in this scenario thanks
i dont understand how to do this (how many table will be required to create) the scenario is thre is a User that is belong from a Zone and zone has a range of IP address that will be generated on zone creation and also on user creation a zone will be selected to select an IP
and if a zone is being deleted the all range of IPs of that zone will also deleted
and one importat thing that user creation time a user must be provided Zone and then he will be assigned an IP from that Zone on deletion of user the IP will set free ** one user must contain only one IP address
We can have userid as PK in USER and both as PK and FK in IP_ADDR to depict one is to one relationship. Put the foreign key constraint as on delete cascade so that when a user is deleted the corresponding ip address is also removed from IP_ADDR to free it up. I suggest you remove columns zone, ip from USER and keep it only in IP_ADDR. Also do you intend to physically delete users from user table or intend to keep them with a deletind in case that same useid may be used for another user? Then u need to have your pk as (userid, deleteind) in both the tables and remember to delete the old entries if there are multiple deletions of an userid
>>i want to relate them into one to one relation ship
I guess I'm just surprised that you want a one-to-one relationship between user and IP. I probably don't understand your need but: Once a user registers (and now has an IP stored) what happens when that user logs in from a different IP? And can two different users share the same IP?