Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    28

    Arrow Unanswered: What makes somthing many-to-one relationship?

    Hello everyone.

    I was confused on what makes a many-to-one relationship. For instance I modeled this .ddl db2 code in Rational Software Architect and its showing its a many to 1 relationship based on the code I'm going to paste.

    The reason Im' asking this is because I'm writing a program and I need to show a many to one relationship so when I model the code it will also appear.

    Here is the .ddl:
    Code:
    CREATE TABLE NODE_OBJ (
    		HOSTNAME VARCHAR(250),
    		MGMT_IPADDR VARCHAR(60),
    		NETVIEW_ID CHAR(32),
    		NODE_ID CHAR(32) NOT NULL,
    		OBJ_ID DECIMAL(20 , 0),
    		SELECTION_NAME VARCHAR(120),
    		STATUS INTEGER,
    		TCPIP_JOB_NAME VARCHAR(25),
    		SYSPLEX_NAME VARCHAR(25),
    		SYSTEM_NAME VARCHAR(25),
    		IS_MANAGED INTEGER,
    		USER_CREATED INTEGER,
    		IS_DELETED TIMESTAMP,
    		IS_DHCP CHAR(1)
    	)
    	DATA CAPTURE NONE ;
    
    CREATE TABLE ITM_SERVICES (
    		NODE_ID CHAR(32) NOT NULL,
    		SVC_NAME VARCHAR(120) NOT NULL
    	)
    	DATA CAPTURE NONE ;
    
    ALTER TABLE NODE_OBJ ADD CONSTRAINT PK_NODE_OBJ PRIMARY KEY (NODE_ID);
    
    ALTER TABLE ITM_SERVICES ADD CONSTRAINT PK_ITM_SERVICES PRIMARY KEY (NODE_ID, SVC_NAME);
    
    ALTER TABLE NODE_OBJ ADD CONSTRAINT NODE_OBJ_NETVIEW_ID_MAP_FK FOREIGN KEY (HOSTNAME)
    	REFERENCES NETVIEW_ID_MAP (HOSTNAME)
    	NOT ENFORCED;
    
    ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
    	REFERENCES NODE_OBJ (NODE_ID)
    	NOT ENFORCED;
    Now here is the image that is produced from that code:
    http://img225.imageshack.us/img225/6104/relzz9.jpg


    As you can see its showing:
    NODE_OBJ is having a 1 to many relationship on the table: ITM_SERVICES

    But through script, what makes somthing a 1 to 1 relationship, a 1 to many relationship or a many to many relationship?

    I see that NODE_OBJ's primary key is NODE_ID and I also see that ITM_SERVICES is using NODE_ID as its foriegn key, so there is a relationship, but what is making that relationship a many?

    Thanks for the help!
    Attached Thumbnails Attached Thumbnails relzz9.jpg  
    IBM: Tivoli zSeries Co-op

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Many : 1 relationship
    Code:
    ALTER TABLE ITM_SERVICES ADD CONSTRAINT
    ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
    	REFERENCES NODE_OBJ (NODE_ID)
    	NOT ENFORCED;
    ITM_SERVICES has exactly one NODE_OBJ.

    Foreign keys shows relationships. So tables where foreign key is created (itm_services in our sample) there is always "many" relationship, but tables where foreign key is referenced to (node_obj in our sample) is "one" relationship.

    1 : 1 relationship
    This is rare situation. In most cases not designed correctly. 1 : 1 relationships should be in one table not two.

    General rule (if designed at all):
    table A, table B
    foreign key on table A references primary_key on table B
    foreign key on table B references primary_key on table A

    Many : many relationship
    Many to many relationships are designed into three tables and "connected" together with 1 : many relationships.

    General rule:
    table A, table b => many : many relationship
    create new "table ab" that gets primary keys from tables a and b

    Sample:
    Code:
    table A
    colA1 (pk)
    colA2
    
    table B
    colB1 (pk)
    colB2
    
    tableAB
    colA1 (pk) (fk to tableA.colA1)
    colB1 (pk) (fk to tableB.colB1)
    Hope this helps,
    Grofaty
    Last edited by grofaty; 10-01-07 at 04:44.

  3. #3
    Join Date
    Aug 2007
    Posts
    28
    Wow! Thanks so much, very clear explanation now it makes sense
    IBM: Tivoli zSeries Co-op

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •