Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Nick.

Asked: July 03, 2002 - 8:37 am UTC

Last updated: July 02, 2012 - 1:45 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

We have an existing application that uses an 8.1.7 database. Our company has hired a consulting firm to design and develop major enhancements for our application. We will also migrate to 9.2 when we roll out the enhancements, but I'm not sure the version is important to my question.

The consultants want to use a join table when there are many to many relationships between 2 tables. The join table would have the primary keys of both the other tables as the data to represent the relationship.

They also want to use exactly the same concept to represent 1 to many (parent/children) relationships. They say that having a third table has many benefits when using todays programming tools.

To me it seems that having to maintain three tables and their indexes, rather that just 2, is unnecessary, and adds overhead.

Is the use of join tables in this fashion considered appropriate these days? We have gotten along just fine without them in the past, but maybe things have changed. I will admit that I learned most of my application development skills in the days of Oracle 5 and Fortran 77.

What do you see as the pros and cons to the use of join tables in this fashion?

and Tom said...

For many to many, you have to use an association table as they are known. This is standard.

For a one to many -- they are "smoking something funny".

Not only does it suffer from the problem you describe, but, it makes it infinitely harder to maintain that 1 to many relationship -- you need yet another additional unique index on the association table in order to enforce the one'ness.

You incurr additional IO overhead in each and every join as well -- as much as:

a) n IO's to index access association table
b) 1 IO to read association table

You could be adding 2-4 IO's nominally to each row joined. For no reason.


I've never heard of anyone using this as a standard operating procedure before in my life. It sounds like a really bad idea. I know of no tool that would be easier to use with this construct. Tell them to THINK ABOUT IT -- there is a 1:1 relationship and a M:1 relationship going on here. They still have a 1:M to deal with -- if 1:M relations were difficult in their tool - well, they still have the 1:M relationship to deal with!

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 03, 2002 - 9:29 am UTC

Tom, Could you please explain more about the concept of "association tables". Is this something new in 9i? I read about bitmap join indexes in 9i. Is it anyway related to that?

Thanks a lot for your enlightening answers!

Tom Kyte
July 03, 2002 - 11:13 am UTC

They are a data modeling thing -- as old as relational databases (at least).

Suppose you have a model:

CLASS ( class_id primary key, description varchar2(25), room number, time date );
STUDENT( student_id primary key, name varchar2, grade number );

A CLASS has many STUDENTS, hence you will need also:

CLASS_ASSIGNMENTS ( class_id references class, student_id references student, primary key(class_id,student_id) );

CLASS_ASSIGNMENTS is an association object, it relates students to classes and vice versa.

Direct and to the point as usual

Nick, July 03, 2002 - 9:37 am UTC

Thanks for the comments.

Can u explain the IO concerned ?

KENNY, July 03, 2002 - 9:43 am UTC

Tom, I am interested in the IO overhead you concerned.
Can u explain more details why there are :
a) n IO's to index access association table
b) 1 IO to read association table

How IO is calculated ?

Thank You !

Tom Kyte
July 03, 2002 - 11:16 am UTC

An index is a data structure. There is a root block, interior branch blocks and leaf nodes.

Lead nodes are where the data of interest to you is -- that has the KEYS and the ROWIDS of the rows in the tables.

In order to get to a leaf node, you need to walk the tree from the root, through the branches to a leaf node. Typically this takes (nominally) 2 or 3 IO's -- root+branch+leaf.

IO is calculated as the number of blocks you needed to read to get the data.

A reader, July 03, 2002 - 12:16 pm UTC


Overcomes adding an FK column to existing table

andrew, July 03, 2002 - 12:36 pm UTC

The technique can used where the aim is to add a parent relationship to an existing table without changing that table's structure (to limit impact to existing the app). Rather than adding an FK column to the existing table - a M:M relationship is created but you only use it as a 1:M. You enforce that by maintaining a 1:1 between the existing table and the new resolution table (by using a unique constraint on the FK end of that relationship.

existing new parent
+------+ +-------+ +-----+
| | 1:1 | | M:1 | |
| |-----| |>----| |
| | | | | |
+------+ +-------+ +-----+

It's a pain to work with and makes the ERD difficult to understand.

Bite the bullet!!!

chuck jolley, July 03, 2002 - 1:16 pm UTC

Adding columns can break some kinds of PowerBuilder code.
But it is MUCH less effort, long term, to fix the code than to create the join table and complicate things for the rest of time.


Why not a view?

Cory D. Wittenborn, July 03, 2002 - 6:02 pm UTC

If adding a column somehow breaks PowerBuilder or some other tool, why not just have PB reference a view that excludes the column? Then you can expose the column to those pieces of SQL that need to see it (either they look at the table itself or another view), and "fragile" code can simply look at the view. If the idea of changing the name that the code is looking at is troublesome, create synonyms for all of your objects in an application schema (a la what Oracle Apps does in the APPS schema)--you can create a synonym for the new view that has the same name as the original table, and a synonym to the modified table (or to a view that includes that additional column) with a different name. Then just have the app login to the new schema--no code change needed for existing code (less, maybe, login stuff that should be in params anyway) and the new code is, of course, new code.

Just a thought...

Tom Kyte
July 04, 2002 - 10:24 am UTC

And a correct thought at that. That is what views are all about...

Attributes to the relationship

sdwarka, July 04, 2002 - 2:22 am UTC

How would you model a relationship that has attributes? If for any reason we have to add / remove attributes from the relationship, the parent or child tables would not be affected because by using a join table we can cleanly model relationships that have attributes. (Ref. Database System Concepts, Korth and Silberschatz, II ed., chapter 2).

Tom Kyte
July 04, 2002 - 11:07 am UTC

So, add/remove columns to the parent or child table as needed.

VIEWS are the physical constructs that allow you to do this without impacting existing applications. Although, truth be told -- existing applications that are impacted by the addition of a non-mandatory/defaulted column are BROKEN way before you add the column!!! That means they are doing a "select *" -- not "select c1, c2, c3" or insert into t values ( ... and not "insert into t ( c1, c2, c3 ) values (...." They are relying on the physical order and number of columns to be never changing. Thats a bug waiting to happen. You should be able to add a column (that allows NULLS or has a default) without IMPACTING any application.

Now, removing a column -- that will affect some applications that use that column REGARDLESS of what table you put it into -- won't it (yes, rhetorical question))


Also, remember, you can LOGICALLY model this however you like. If you like ERDs with association objects for every relation, go for it. Physically, they should be in the table when you have a 1:m relationship. The physical doesn't have to be the same as the logical model.

There is a huge difference between a LOGICAL MODEL and a PHYSICAL MODEL.

warning: opinion coming up...

I also find for pragmatic reasons that people who over analyze what is a fairly simple problem (like going for the 100th normal form or something like that) spend far far too much time "modeling" and not enough time "doing".



how do you bring in records from tables with M:M relationship

A reader, April 21, 2004 - 5:13 am UTC

Tom,

I was asked by one of my friend, how do you bring in records from tables with M:M relationship. I dont know how to answer, sorry about it.

there are few tables ( or have 3 tables) with many to many relationship, how do you bring the records without any duplication and suppressing any records. - by what method you can bring them out --- joins???? but how doyou prove that theortically, if not joins what could it be?

can you please help me out in this.Thanks for your time and consideration

Tom Kyte
April 21, 2004 - 8:09 pm UTC

question makes no sense.

give me an example of inputs and show what the possible output would be and explain how you arrived at that output.

1:m and 1:n relationships

A reader, November 12, 2004 - 4:40 pm UTC

I have 2 views

a. cust_address (1:m, customer has multiple addresses)
b. cust_accounts (1:n, customer has multiple accounts)

When I join the above 2 views on the cust_id, for a given cust_id, I get m*n rows with everything "mixed" together as a database join does :-(

How can I get m+n rows instead, with the m rows from cust_address separated out from the n rows from cust_accounts?

Thanks



Tom Kyte
November 12, 2004 - 8:45 pm UTC

that would be called "a union", especially since you said "i want them separated out"

union a query against cust_address with a query against cust_accounts

One-to-Mant, Eh

Dave, July 19, 2005 - 4:32 pm UTC

The one-to-many relationships that the consultants were talking about may have been from the pre-database days.

Back then, when you found numerous children in one data file, that could be associated to the parent records of another data file, you would create a third data file for that association.

Today, that third data file is called an "INDEX". Many programming tools do make use of that third table, and refer to the data environment as a Relational Database.


how to model LinkedIn connections

Mark E, November 12, 2009 - 8:27 pm UTC

Tom, I want to model a self-referential many-to-many relationship when there is no explicit ordering between the two entities. For example, a LinkedIn connection. I'm connected to you and you are connected to me. In this relationship I would be in either column A or B of the association table. What is the best way to model this to avoid issuing a double query if I wanted to check if two people are connected e.g. (A=Mark and B=Tom) OR (A=Tom and B=Mark)? Thanks


Tom Kyte
November 15, 2009 - 2:25 pm UTC

typically, one would insert both

(a,b)
(b,a)

in such a situation.

or, if that isn't the way you want to go

select * from people p1, links, people p2
where p1.key = links.key1 and links.key2 = people.key
and p1.key = :x
union all
select * from people p1, links, people p2
where p1.key = links.key1 and links.key2 = people.key
and p2.key = :x;


that goes from p1 -> links -> p2 PLUS p2 -> links -> p1



Learning SQL

kiran, July 02, 2012 - 10:24 am UTC

Hi Tom,

I am trying to become a sql developer. I learned about sql and it's syntax from Oracle documentation but I am having hard time in joining the tables while writing queries. I have seen some of your replies where you have asked for whether the relation between the 2 tables is 1:1/1:M and depending on that you have provided the solutions. I think I am lacking in relation database concepts. Could you please let me know if we have Oracle documentation on RDBMS concepts? or guide me through some documentation where it covers these concepts?

Please help me on this. Appreciate all your help.
Tom Kyte
July 02, 2012 - 1:45 pm UTC

Oracle documentation does not teach relational theory/concepts no.

there are many books out there and I might suggest this series of articles:

https://www.google.com/webhp?q=site%3Awww.oracle.com%20caffrey%20sql%20101

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library