Click here to Skip to main content
15,070,111 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am working on my final year project of bscs degree. em struggling with the database design as i counter a problem of one to one relationship .I hope you will understand my problem and suggest me a finest solution .
There are three tables
1.DomesticUsers(domesticuserid,username,location)
2.CommercialUsers(commercialuserid,username,location)
3.ElectricMeter(meterno,meterphase)
only one meter is alocated to one user either domesticuser or commerical user

Now where should i place the foreign keys ..??
Posted

DomesticUsers and CommercialUsers tables must each have a foreign key
containing the primary key of the ElectricMeter

SQL Relationships are well explained here:
http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561[^]
   
If i understand you correctly, i suspect bad table design!

It should be only one table for users: MyUsers(uid, uname, tid, eid),
where tid is Foreign Key and reference to tid in TypeOfUser(tid, tdescription)
and
where eid is Foreign Key and reference to eid in ElectricMeter(eid, meterphase).

Note: there must exists data in TypeOfUser and ElectricMeter tables to be able to add user.

See: SQLFiddle[^]
   
If it is not working, change the design:
SQL
meter (meterno, meterphase)

user (userid, usertypeid, username, location, meterno)

usertype (usertypeid, usertypedesc)


where usertypedesc can have the vaues of domestic, commercial, etc.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900