Re: Help! Primary Key/Normilization Questions?

sehlke_at_cts.com (Steve Ehlke) wrote:
>
>I am migrating a MS Access application over to a VB client and a
>Oracle 7.1 server. The existing application contains some tables that
>have columns which are 95% unique but none that are 100% unique and
>use a counter column (Access provides a counter data type that
>increments automtically) to provide the uniqness for the primary key.
>This key is then used as the foreign key in numerous other tables.
>
>The questions I have are:
>
>- Is there a way in Oracle to simulate the Access counter type? I
>tried the Oracle type ROWID but no luck.

ROWID is a guaranteed unique key for the row, but it is not at all the same thing as the "counter" type in MS Access (in actuality, it is the location of the row in the disk file and is analogous to the "head-track-sector" address used in a disk controller). You can get sequential, guaranteed unique values by using a sequence - see your Oracle documentation for the details.

>
>- Do I want to simulate this, or should I normalize the tables even
>more? If I do this, I'll almost double the number of tables I have.
>Is this worth it to get 100% normilization? Also, one of the tables
>that will use these keys as foreign keys will be > 1 million records.
>The Access counter type used 4 bytes. If I normalize the table, the
>primary key would be 30 bytes. By normalizing without a counter I add
>26MB to my table size for this one table. Is this good practice?
>(Does Oracle work this way, or are foreign keys stored as a pointer
>instead of the actual data which would not require the additional disk
>space. Any insight?)

A foreign key is stored as whatever it is, not as a pointer per se. However, ask yourself this question: is there an attribute (field) in the data that can naturally be used as a key (primary or foreign)? If so, use that attribute as your key rather than generating a surrogate (counter, sequence value). That way, if you are ever forced to rebuild tables from scratch (yeah, you're not *supposed* to ever have to do this, but ....) you don't have to rely on re-entering the data in the same order, with the same references, etc. Plus, you don't use up storage for the (essentially redundant) "system-assigned key" you generated from a counter/sequence. (They DO take up space). BTW, look in the Server Concepts manual to see how Oracle stores numbers - it is **NOT** what you might expect and an "integer" is certainly NOT a 4-byte number like you might think.

In general, proper normalization should minimize storage space (though in individual situations, your mileage may vary). Best bet: make sure your data model is in at least 3rd-normal form, using natually-ocurring keys as primary keys/foreign keys wherever feasible.

>
>- Is there a way to combine columns to form the primary key?
>

No sweat. You may include as many as 16 columns in a primary key (though you really should take a hard look at things if you go more than 2 or 3).

>ANY help would be MUCH appreciated!
>
>Steve...
>
>
>

Hope this helps.

-- 
Bill Meahan  wmeahan_at_sun1.fsic.ford.com
Ford Motor Company -- End User Support - North America
Not an official statement of Ford Motor Company or anyone else
except the author (though my dog tends to agree with all I say)