DB Guidelines 1

[Andrzejek]

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.
[ul]
[li]Every table has to follow 1st, 2nd, and 3rd Normal Form[/li]--- 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.
[li]Every table has to have a numeric Primary Key[/li]
[li]Number of fields in a table should not exceed a ‘reasonable’ number (up to 20? 25?)[/li]
[li]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)[/li]
[li]A table has to relate to any other table only by one field (Foreign Key to Primary Key)[/li]
[li]Any piece of data has to be in one place only. No piece of data can be copied to two or more places / tables.[/li]
[li]There should be a standard established for naming any object: table, field (specific for PK, FK), index, trigger, sequence, check, etc.[/li]
[li]Names of objects should be as short as possible, but long enough to be readable.[/li]
[li]No calculated, concatenated (etc.) fields in a table.[/li]
[li]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. [/li]
[li]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.[/li]
[li]Each table represents one object (entity) in the real-world systems it represents.[/li]
[li]Anything else? [/li]
[/ul]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

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

 

[Andrzejek]

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:[pre]
FieldA FieldB Sum
200 300 600[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

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

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

 

[Andrzejek]

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

I will change:[ul][li]No calculated, concatenated (etc.) fields in a table.[/li][/ul]
to
[ul][li]Use virtual columns to show calculated, concatenated (etc.) data in a table.[/li][/ul]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

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