Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mkkb917
Starting Member
14 Posts |
 Posted - 2014-03-28 : 10:58:05
|
| hi dearsi am new developeri 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.254and 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 usernow plz tell me how to create this relationship or i am missing something or not doing goodi am confused with making PK on both tables userid is as PK and in second table i set PK both IP and Zonenameam i right plz guide me in this scenario thanks |
|
|
mkkb917
Starting Member
14 Posts |
Posted - 2014-03-31 : 12:47:00
|
| i need help in Database creationi dont understand how to do this (how many table will be required to create)the scenario isthre is a User that is belong from a Zoneand 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 IPand if a zone is being deletedthe all range of IPs of that zone will also deletedand one importat thing that user creation time a user must be provided Zone and then he will be assigned an IP from that Zoneon deletion of user the IP will set free** one user must contain only one IP addressthanks |
 |
|
|
sumitava20
Starting Member
4 Posts |
Posted - 2014-04-18 : 02:43:56
|
| 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 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-04-18 : 10:57:02
|
| >>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 OptimizerTG |
|
|
|
|
|
|
integrity problem