What makes somthing many-to-one relationship?

mr_coffee · #1 (**permalink**) 09-30-07, 06:46

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!

grofaty · #2 (**permalink**) 10-01-07, 03:41

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

mr_coffee · #3 (**permalink**) 10-01-07, 10:11

Wow! Thanks so much, very clear explanation now it makes sense

Internet Services

Web Development

Digital Marketing

Consumer Tech