Re: ORACLE DB Design on PC Client/Server

This should generate quite a few responses...

1. 7 vs 8. This issue boils down to, do I go with a tried and true product or something hot off the presses with undiscovered bugs. I think that most consultants will give you the tried and true answer. Especially for mission critical systems. I agree.
2. The Oracle RELATIONAL database management system seems to go hand and hand with ER Models. Keep in mind you can fit an Object Model to a relational database fairly easily.
3. This is an issue of performance. So it may be hard to answer without more information about your system. Generally, speaking normalizing is a good thing. It produces more smaller tables. Denormalized produces larger tables.
4. This is really system specific. I would generally avoid using a counter or artifical id for a primary key. I have been on several projects where data migration/integration between environments becomes impossible because these ids (usually sequence based) get out of sync. Personally, I would go with a mulitvalue primary key. There is no such thing as almost unique.
5. The foreign key relationships are almost always benefical use them liberally. The many to many relationships are usually resolved just like you said with a crossreference table consisting of the keys from other tables. This is standard practice.

On 12 Sep 97 11:14:19 IST, mttrivka_at_matat.health.gov.il wrote:

>
>We are having an outside firm build a new ORACLE 7 database application
>for us, which will run on IBM WINDOWS-NT Client/Servers. The application
>will replace two existing INGRES database applications. At this point
>the firm is designing the database, screens and reports on paper only.
>We need to give them feedback on whether to make any revisions or to
>proceed to implement the system. Please excuse the simplicity of these
>questions since we are not familiar with ORACLE on IBM PC Client/Servers.
>1. Are there good reasons to build the system with ORACLE 7 rather
>than ORACLE 8?
>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?
>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?
>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?
>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?
>
>Thank you for your attention to these questions.
>Sincerely,
>Rivka Fishman and Simcha Book
>Ministry of Health, Israel
Received on Fri Sep 12 1997 - 00:00:00 CDT