3 Replies - 840 Views - Last Post: 31 August 2010 - 04:16 PM

#1 alowais  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 13-September 08

Connecting four tables in mssql express

Posted 27 August 2010 - 06:25 PM

Hi guys,

I need your help with this database design, I've three 4 tables:

1- user_login_tbl
2- org_details
3- emp_details
4- ca_emp_details

the first table is for users to login to their account, the second one is to allow them to insert their org details, the third belongs to the org to fill in their employees' details, the last one is similar to the previous one.

I've linked the two first tables with user_login_tbl(PK, FK ; user_id) and org_details (PK; org_id, FK; user_id)
The last two with (FK; org_id), however when I insert data into the user_login_tbl it doesn't chanage or updated in the other tables I mean the user_id is not shown in the org_details,and when I insert into the org_details the org_id is not changed or added in the other two tables.

when I create the digram in the sql express the relations looks fine.

I used "int" datatype for the org_id and user_id identity (1,1).

the questions are:

Is it necessary to see the foreign key changed in the other table whenever it's updated in some other tables?
What a good way to make sure the tables relations are ok?
What are your suggestion to fix this problem?

Thanks,

Is This A Good Question/Topic? 0
  • +

Replies To: Connecting four tables in mssql express

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Connecting four tables in mssql express

Posted 29 August 2010 - 10:02 PM

Establishing table relationships doesn't cause records to be created in the related tables. A relationship assures that records cannot be added inappropriately. For instance, employee record cannot refer to an orgID that is not yet in the org table. Without relationship anything could be put into the orgID field and the employee record would be accepted. A relationship can also cascade edit of PK. If you change the PK of org record then the FK in related employee records would reflect the change. Still have to go to each table and add the records. So working directly with tables is not the best way to go about data entry. I never set up an application to permit this. Create forms as data entry interface. Use form/subform structure with Master/Child linking. This will facilitate adding records without having to hop around tables.

This post has been edited by June7: 29 August 2010 - 10:08 PM

Was This Post Helpful? 0
  • +
  • -

#3 alowais  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 13-September 08

Re: Connecting four tables in mssql express

Posted 31 August 2010 - 04:10 AM

View PostJune7, on 29 August 2010 - 09:02 PM, said:

Establishing table relationships doesn't cause records to be created in the related tables.


When I insert into one of the tables, I get an error because the FK does not allow null in the other table.
I don't know something is wronge but I've created the tables and if u look at its digram in sql it looks fine !!

I used this "FK(org_id) references emp_tbl(emp_id) on update cascade"

and before this I tried others.
Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Connecting four tables in mssql express

Posted 31 August 2010 - 04:16 PM

I don't use SQL database, just Access. But as far as I know the principles of table relationships are the same. Relationship does not permit entry into FK field a value that does not already exist in the primary table. You have to input the primary record first, then the PK will be available for entry into the dependent table FK field. Use a combobox with a RowSource of the PK field to aid users in data entry in the FK.

Quote

I used this "FK(org_id) references emp_tbl(emp_id) on update cascade"

and before this I tried others.
My understanding of 'update cascade' is that if you change the PK in the primary table the value will be changed in the FK field of the dependent table records.

This post has been edited by June7: 31 August 2010 - 04:24 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1