SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 integrity problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mkkb917
Starting Member

Pakistan
14 Posts

Posted - 03/28/2014 :  10:58:05  Show Profile  Reply with Quote
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 1.0.0.1 to1.0.254.254
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

mkkb917
Starting Member

Pakistan
14 Posts

Posted - 03/31/2014 :  12:47:00  Show Profile  Reply with Quote
i need help in Database creation

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

thanks
Go to Top of Page

sumitava20
Starting Member

India
4 Posts

Posted - 04/18/2014 :  02:43:56  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/18/2014 :  10:57:02  Show Profile  Reply with Quote
>>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?


Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000