April 18, 2014 at 6:07 am
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
April 18, 2014 at 8:47 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2014 at 9:58 am
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 ?
April 18, 2014 at 10:24 am
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
April 18, 2014 at 10:32 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2014 at 12:01 pm
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
April 18, 2014 at 12:28 pm
mkkb917 (4/18/2014)
ok siri 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply