Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql tables realtions Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 17, 2014 10:44 PM
Points: 8, Visits: 5
hi i m mkkb and new in sql server databases
i m using Visual Studio which use sql server express edition
i am working on a project for practice (my big bro project) and get hang up on creating database diagram(relations between tables ) as i am confused on this

i am sharing my problem to my seniors hoping that i get some help

it is an ISP provider (local wifi) database which will manage the user data and their payments(bills)
i created following tables

USER[UserName(PK), Password, Address, PhoneNo, RegDate, RegFees, MAC, IpAddr, Pckg, Contact]
STATUS[ ActvDate(PK), UserName(FK), ActvMonth, ActvFee, Status]
BILLING[BillDate(PK), UserName, Month, TotalBill]
ZONE [ ZoneName(PK), CrtnDate]
IPPOOL [IpAddress(PK), ZoneName(FK), UserName(FK)]
REFFER [ ReferedBy(PK), Month, UserName]

now the scenario is
a user is get registered with a single unique user name .and MAC address and IP address is allotted onthe time of creation .

Zone is a geoghraphical area which contain a lot of users under this also have a specific IP range caled IP Pool for example zone name xcity and IP Pool is 192.168.7.1 to 192.168.8.255 . so all users that belong to this "xcity" must be allocated a unique ip from that pool .

Billing of a user started with the activation status of the user . if user is activated he/she must pay his monthly bill

IPPool table contain a range of IPs from a specific zone and also the UserName that is allocated to that IP address

REFER table just able a user to refer a new user to the system

now my problems are
1) i create a one to one relationship between USER and IPPOOL table ( as one and only one ip can be allocated to a user at a time) if i delete the user the IP from IPPOOL table also get deleted . and also i want that on the deactivation of User Status the IP from IPPOOL of that user get released so that it can be allocated to some other user. For this i dont understand how to do that
i want only the IPs from pool is allocated and reallocated to users

2) i created one to many relationship between USER and ZONE tables as one ZONE may contain 0 or many Users and it is fine . also created one to many relationship between ZONE and IPPOOL tables because one IP must be from a Zone and a Zone has a range of IP addresses and its also fine ( i successfully generated the IP range in vb )

3) i created a STATUS table which is related to USER table on 1 to many realtionship as one user accout may have many status ike active , inactive . and here if the user is active , its billing will be started and if user got inactive status it will not pay his bill and also the IP will aslo get released (that is the main problem)

plz tell me if my tables are correct or i need extra table or column to be created

thanks in advance
Post #1562985
Posted Friday, April 18, 2014 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
Hi and welcome to the forums. You are far more likely to receive responses if you post the ddl (create table script) for your tables. That way we can really see what you have going on.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563064
Posted Friday, April 18, 2014 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 17, 2014 10:44 PM
Points: 8, Visits: 5
sir thanks for replying immediately

sir i am using sql server express edition in Visual Basic 2010 and there i create the tables and relations using the graphical interface

i created some relations and modify some tables
including a picture of the relation that is generated
i think it will help

BILLING table wil be related to STATUS table and IPPOOL table will be related to ACCOUNTS table
but how ?


  Post Attachments 
dbdiagram.png (3 views, 233.20 KB)
Post #1563091
Posted Friday, April 18, 2014 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 17, 2014 10:44 PM
Points: 8, Visits: 5
my big problem is the relation between IPPOOL and ACCOUNT table which will be created to 1 to 1
and that mean that if i delete a user also will delete the IP related to user
and i want to just remove the user not IP
Post #1563104
Posted Friday, April 18, 2014 10:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
I would like to make a number of recommendations. First of all you should not use really short abbreviations for things. PCKG I assume is Package? Reg_Date...is the RegularDate, RegistrationDate, RegurgitationDate. Your names are all far too generic to be very useful. Status does not give any kind of indication what it means. AccountStatus is a LOT easier to understand.

In general you seem to want to use natural keys, this is ok but you have some issues with that. Under your current structure a User is unable to change their username. Also, you should not have a column change names between tables. This makes things really painful to work with. Why is IP in the Accounts table? Shouldn't that be in the IPPool table?

Not really sure what you are trying to accomplish here but the relationships and structures you have established need some work. I would recommend on reading up a bit on referential integrity and normalization.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563110
Posted Friday, April 18, 2014 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 17, 2014 10:44 PM
Points: 8, Visits: 5
ok sir
i first change my naming conventions to as u described me
and can u suggest me some article on normalization
so that i can read
Post #1563124
Posted Friday, April 18, 2014 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
mkkb917 (4/18/2014)
ok sir
i first change my naming conventions to as u described me
and can u suggest me some article on normalization
so that i can read


Do a search on this site for "normalization" Tom Thompson has a great series of articles about the topic (search only in articles). http://www.sqlservercentral.com/search/?q=normalization&t=a&sort=relevance

Also, Joe Celko has good piece in the stairways about normalization.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse