Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: One to One Relationship

Re: One to One Relationship

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: 19 Mar 2003 09:56:45 GMT
Message-ID: <Xns9343D26792DA1Tokenthis@210.49.20.254>


Following up on Tim X, 19 Mar 2003:

>
> If its a true 1:1 relatinship, nothing is lost by merging into one
> table from a theoretical standpoint. However, as others have pointed
> out, breaking a large table into two or more can have performance
> advantages and offer additional levels of security etc.

Not only that. Imagine the following:

Entity: Aircraft types.

Look at an FA18. It has a number of attributes, like 2 engines, one cockpit, one seat, one type of pilot, one type of radio equipment, one type of armament configuration if it is a fighter model, etc.

Now, look at a C130. It has a much LARGER number of attributes. 4 engines. Each with a potential maintenance hour rate. One cockpit, but a number of seats in it that varies even within a mission. More than one type of pilot, depending on its config and if it is to fly recon, supply or gunship. And so on.

Most of these have no bearing whatsoever on an FA18, but are needed for a C130. They can easily be described as a 1:1 to the aircraft type, but are completely optional for exampe for a P3-C Orion, which has its own set of unique demands.

So, what do you do? Store the whole kit an kaboodle as potential NULL columns in a single AIRCRAFT_TYPE entity? No thanks, that's almost impossible to index properly, with all those combinations of NULL values. Much better to isolate what is common between them all aircraft types into a single table, then have "auxiliary" tables on a 1:1 for the specific types and their quirks.

This technique used to be called sub-typing. I don't know if the term is still "politically correct", but any subtype is potentially a 1:1 between two tables. Depending on how you choose to implement the sub-type, of course. It can still be one table!

Using multiple tables, you achieve a number of things:

1- You don't have a monster table (heaps of columns) most of the time only partially filled in various combos. 2- You can have as many different types of aircraft and their specifics as you may ever want: just create another subtype for each aircraft type that really needs extra stuff.
3- You have the potential for much more compact and efficient indexing for the fiddly bits in each of these auxiliary tables. If you ever need it.
4- If you use an object type to access the data, you don't have to have a very large one. A few simple ones make up for much faster JDBC access.

Drawbacks:
1- Potentially increased schema size. (more than one table for same entity) 2- Application logic has to know what subtype it's dealing with. This can be a problemn in some cases.

Etc,etc.

Just another case where the canonical form is fine, but reality says we should look at alternatives. Sub-typing is the most obvious of the 1:1 cases that can be useful.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
Received on Wed Mar 19 2003 - 03:56:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US