What makes somthing many-to-one relationship?

[voidflux]

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:

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!

[davide++]

Hi all.

You've created NODE_OBJ table with key on a single column, and ITM_SERVICES with key on two columns, so you got a 1-M relationship based on NODE_ID fields.

Infact you can insert into NODE_OBJ the record
NODE_ID HOSTNAME
A0001 MyHost
and then you can insert into ITM_SERVICES the records
NODE_ID SVC_NAME
A0001 MyName
A0001 YourName
A0001 OtherName

This means that you link many records in ITM_SERVICES to one record in NODE_OBJ, ie 1-M relationship.

[andreasblixt]

A one-to-one relationship means that a row in one of the two tables can only ever refer to one single row in the other table. This is achieved by making the columns in both tables that are used by the relationship only allow unique values.

Table 1
+----+----------+··
| ID | Table2ID |
+----+----------+··
|  1 |        3 |
|  2 |        2 |
|  3 |        1 |
|  4 |        1 | <-- Error! Value '1' has already
+----+----------+··   been used in this column.
Primary key: ID
Unique key: Table2ID

Table 2
+----+··
| ID |
+----+··
|  1 |
|  2 |
|  3 |
+----+··
Primary key: ID

In the above example, only one-to-one relationships are allowed between Table 1 and Table 2.

Generally the model is even simpler than in the example, with a two-way relationship on the primary keys of both tables.

One use for a one-to-one relationship could be to have one table for vehicles which hold general data for all vehicles, then separate tables for different types of vehicles that hold specialized data for that particular vehicle type. The specialized tables would share the same primary key as the row in the general table. This could be thought of as extending the base table (kind of like OOP.)

A one-to-many relationship means that a row in one table can be referred to by multiple rows in another table. This is achieved by making a column with the same data type as the primary key column in the "parent table" (the table that is referred.)

Table 1
+----+----------+··
| ID | Table2ID |
+----+----------+··
|  1 |        2 |
|  2 |        2 |
|  3 |        2 |
|  4 |        1 |
+----+----------+··
Primary key: ID

Table 2
+----+··
| ID |
+----+··
|  1 |
|  2 |
+----+··
Primary key: ID

In the above example, three rows in Table 1 refer to the row in Table 2 that has ID #2.

One use for one-to-many relationships is for linking a forum post to a thread. A thread can contain many posts, but a post is always located in only one thread.

In many-to-many relationships, one table can have several rows that refer to a single row in another table, but that other table may also have several rows that refer to a single row in the first table. This is achieved by creating a middle-ground table that holds all the relationships.

Table 1
+----+··
| ID |
+----+··
|  1 |
|  2 |
|  3 |
|  4 |
+----+··
Primary key: ID

Table 2
+----+··
| ID |
+----+··
|  1 |
|  2 |
|  3 |
+----+··
Primary key: ID

Relationship table
+----------+----------+··
| Table1ID | Table2ID |
+----------+----------+··
|        1 |        2 |
|        1 |        3 |
|        2 |        1 |
|        2 |        2 |
|        2 |        3 |
|        3 |        2 |
|        4 |        1 |
|        4 |        2 |
|        4 |        3 |
+----------+----------+··
Primary key: Table1ID + Table2ID

In the above example, two rows in Table 1 refer to the rows with IDs 1, 2 and 3 in Table 2.

One use for a many-to-many relationship is to store friendship lists on a social networking site. One user can be befriended by a lot of users, while at the same time that user can have many users as friends. The middle-ground table can be used to store meta-data that is just for the particular relationship in question (such as how the friends met.) Note that in the above example, mutual friendship isn't obligatory; one user could befriend another, while that other user hasn't befriended the first user.

[davide++]

Hi all.

In addition to the clear and accurate andreasblixt's explanation (good job andreas) I would say that one-to-one relationships are rather rare.
A good reason to use them is when we have to manage "general table and specialization tables", as andreasblixt said, but personally I dislike this way to design the database, because usually there are performance problems: the general table tends to grow continuously, and if I want to select the general data of a small specialization table I have to query the bigger general table; moreover, extracting the specific from specialization table data often requires joining the two tables.

[andreasblixt]

Yup, one-to-one relationships are rare and should only be used when you actually benefit from them.

For example, if you have a table with 50 columns and for most of the rows 30 of those columns are NULL, then you could move those columns to a secondary table. This will reduce the page size for the base table which improves index speed.

I'm not so sure what the performance gain is if you always do a LEFT JOIN to retrieve the columns in the second table (this way it'll return the exact same dataset as before the split), but if you only retrieve the 20 columns from the base table most of the time, there will definitely be a performance gain from splitting the tables.

[voidflux]

Thanks guys! Very nice explanation

[technoroj]

Thanks guys! Very nice explanation

Hi all,

Many to one relationship is nothing but many rows of one entity are joined with a single row of second entity.

for example
entity 1 contains
driver
conduction
tickets
passengers

entity 2 contains
Bus

now if you join both of this entities, the relationship will be (if 1->2) Many to one !


"Use of SQL Joins (Left join) is one solution of your database design"

[cjard]

Thanks guys! Very nice explanation

Consider the notion that:

If two tables are related and will only ever be 1:1 relation, they should probably be the same table

Databases only ever work effectively when they contain 1:M relations ONLY.
1:1 shouldnt exist (above) and neither should M:N

[andreasblixt]

Consider the notion that:

If two tables are related and will only ever be 1:1 relation, they should probably be the same table

Databases only ever work effectively when they contain 1:M relations ONLY.
1:1 shouldnt exist (above) and neither should M:N

While many-to-many relationships can be avoided in a seldom changing database, in larger, actively changing databases they're almost always a necessity. Just look at this thread; I can almost guarantee you that each post in it is a many-to-many relationship node.

Why? There are users in this forum. There are threads in this forum. This thread is one in particular and it contains posts. Each post refers to the thread, and to the user that made the post. Ergo, many posts in a thread made by many users.

Now you might argue that this is in fact two 1:M relationships. Yes, that's true, but that's how you implement many-to-many relationships.