Re: ORACLE DB Design on Client/Server

Shalom, Rivka Fishman and Simcha Book!

mttrivka_at_matat.health.gov.il wrote in article <1997Sep12.111710.1_at_matat>...
> 1. Are there good reasons to build the system with ORACLE 7 rather
> than ORACLE 8?
Oracle 7.3 is probably considered more stable. I haven't checked but it's probably cheaper. But Oracle 8 supports far more users, transactions, and allows the database to be distributed with additional features Oracle 7 doesn't support. But you can still do web design for 7.3. Oracle 8 is the only choice if you want Network Computer support (see Oracle's web site and www.nc.com). Oracle 8 supports objects, both in the tables and in PL/SQL, which may lend itself to a more robust design for very large projects. Smaller projects are probably better off done without objects. However, Designer 2000 and Developer 2000 are not supported on Oracle 8, and Sedona is not to be released anytime soon (it was originally written in VB and is now being redesigned for Java).

> 2. Is the Entity-Relationship Model the method of choice for designing
 this
> type of database? What other choices are there? What are the advantages
> and disadvantages?
Go to www.oi.com and see what Peter Coad has to say about object-oriented design. He has several design tools that are quite excellent, supporting not just his methodology but a few others as well (like Booch Unified). If you go with 7.3 I'd stick with ER diagramming.

> 3. Is it recommended to de-normalize tables by splitting them up into
> smaller tables in order to obtain efficient performance in this type
> of system?
Nobody normalizes 100%. Some denormalization is perfectly acceptable (like specifying 2 address lines in an addresses table, or not breaking down a phone number or zip code into its components). You make the call based on how useful the further breakdown will be to you. You don't de-normalize tables by splitting them up; just the opposite.

> 4. Is it preferable to have the "primary key" for each table to contain
> (or consist of) a counter rather than have a combination of meaningful
 fields?
> If a row depends on multiple fields, should the "primary key" be defined
> as unique and consist of all those fields, or is it enough to define it
> as the first field which would be almost unique?
The primary key should be an incremental number, such as can be supplied by a sequence object. Oracle can lookup primary keys that are numbers faster than alphanumeric values. If you need to lookup on a unique combination of columns, define a unique key constraint for it. The primary key is most useful when you need to make a foreign key reference to this table from another table.

> 5. Is it recommended to add "foreign keys" to tables to facilitate all
> anticipated one-to-many joins? What about building tables consisting only
 

> of "primary keys" from two other tables to facilitate many-to-many joins?
For many-to-many relationships, you have to define a tertiary table that contains just the primary key columns from the two tables being related. The foreign keys are most useful for secondary or master table lookups, and will definitely help maintain referential integrity (which is generally critical to avoid "garbage" data). The foreign key is used to locate child or master entries in related tables. For simple tables with few rows that consist solely of an alphanumeric code and a full description, don't bother with a numeric primary key-- just use the code. Make sure you maintain the relationships between tables using ER diagrams. It's the only way you'll keep things straight. - Dan Clamage dclamage_at_idcomm.com Received on Tue Sep 16 1997 - 00:00:00 CEST