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!
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 !
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...
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).
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
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
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
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.