INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DB Guidelines

DB Guidelines

(OP)
I have this data base in Oracle - established by somebody else - and now this ‘somebody’ is willing to re-do it and make it right (I hope). So I am trying to come up with some rules that this new data base should (must?) follow. Most (if not all) of the stuff below is obvious, but I want to state it anyway just to make sure it is stated (I hope that makes sense).

Feel free to add to it - rules from your own experience so others can benefit.
  • Every table has to follow 1st, 2nd, and 3rd Normal Form
  • --- First Normal Form (1NF): A table is 1NF if every cell contains a single value, not a list of values. This properties is known as atomic. 1NF also prohibits repeating group of columns such as item1, item2,.., itemN. Instead, you should create another table using one-to-many relationship.
    --- Second Normal Form (2NF): A table is 2NF, if it is 1NF and every non-key column is fully dependent on the primary key.
    --- Third Normal Form (3NF): A table is 3NF, if it is 2NF and the non-key columns are independent of each others. In other words, the non-key columns are dependent on primary key, only on the primary key and nothing else.
  • Every table has to have a numeric Primary Key
  • Number of fields in a table should not exceed a ‘reasonable’ number (up to 20? 25?)
  • Fields in a table have to have NOT NULL setting with default values (unless there is a very good reason not to. Example: comments field)
  • A table has to relate to any other table only by one field (Foreign Key to Primary Key)
  • Any piece of data has to be in one place only. No piece of data can be copied to two or more places / tables.
  • There should be a standard established for naming any object: table, field (specific for PK, FK), index, trigger, sequence, check, etc.
  • Names of objects should be as short as possible, but long enough to be readable.
  • No calculated, concatenated (etc.) fields in a table.
  • If a data element contains two or more components, they should be divided as much as possible. The data element can be rebuilt by concatenating the individual components.
  • Identify the columns having an index – those having foreign keys, those used frequently in search conditions or joins, those with a large number of distinct values and those updated infrequently.
  • Each table represents one object (entity) in the real-world systems it represents.
  • Anything else?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: DB Guidelines

I agree with most of your rules, except the following

Number of fields in a table should not exceed a ‘reasonable’ number (up to 20? 25?)
You need as many columns as is necessary to hold the data. If it is 2, fine. If it is 100 fine. Breaking up a logical table to follow some limit is illogical.


A table has to relate to any other table only by one field (Foreign Key to Primary Key)
This rule makes no sense. For example you have an employee table with a column for company and a column for division, and a column for sub-division. This table would have 3 foreign keys pointing to 3 different tables.


No calculated, concatenated (etc.) fields in a table.
I would typically agree with this but with the existence of virtual columns the ability to pre-define a calculated or concatenated value is too useful.

Bill
Lead Application Developer
New York State, USA

RE: DB Guidelines

(OP)
Bill,
While in principal I agree with you, let me explain my position.
‘Reasonable’ number of fields in a table: one of the major table in the current DB has 230+ fields, no PK, 99% of them allow NULLs, another table: 160+ fields, no PK, all of them allow NULLs. They should be logically divided into about 10 tables each. I don't want 25 fields to be 'set in stone', but more reasonable than 200. I hope you agree.

I may not explain myself clear enough for your second point. What I meant was: I don't want 2 tables to be connected by 5 fields in order to get the correct results. That's what I have now and it is a nightmare.

Your 3rd point - I would rather use Views to accomplish that, and not have:
FieldA  FieldB  Sum
200     300     600 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: DB Guidelines

No need for views when you have virtual columns for example your example could be defines as

CREATE TABLE HR.TEST
(
FIELD1 NUMBER,
FIELD2 NUMBER,
SUM_TOTAL NUMBER GENERATED ALWAYS AS ("FIELD1"+"FIELD2")
);


after the virtual column is defined the database takes care of everything

CODE -->

INSERT INTO HR.TEST(FIELD1,FIELD2) VALUES(10,23);

hr@orcl>SELECT * FROM HR.TEST;

    FIELD1     FIELD2  SUM_TOTAL
---------- ---------- ----------
        10         23         33 





Bill
Lead Application Developer
New York State, USA

RE: DB Guidelines

(OP)
Thank you Bill,
I was not aware of 'virtual columns' in Oracle (I guess they are called 'computed columns' in SQLServer)

I will change:
  • No calculated, concatenated (etc.) fields in a table.
to
  • Use virtual columns to show calculated, concatenated (etc.) data in a table.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: DB Guidelines

I forgot to mention that virtual columns started in oracle 11. But there great if you have 11 or higher

Bill
Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close