Database Design 101

[Andrzejek]

I am trying to put together some ‘points’ and organize a class for basic Oracle relational data base design 101 Something basic, very basic that all data base users (developers) and especially people who design the data base should (read: must) know and follow. On one hand I would like to cover basic, basic stuff, and not to go too deep, on the other hand I would like it to be the base to go deeper into the subject later (in another class, maybe).

If anyone can add to it or modify it, I would appreciate it.
---------------------[blue]
ORACLE Relational Database Design 101
Using Tools for Oracle Application Development (TOAD)

Definition of ‘database’
Definition of ‘relational’

Table: Create, Alter, Rebuild, Truncate, Drop

Tables:
• Naming conventions
• Naming size limits
• Structure
• Look-up tables
• Limits of numbers of fields in a table (logical vs. Oracle’s limit)
• Fields:
[ul]
[li] Types[/li]
[li] Size[/li]
[li] NULLs[/li]
[li] Default values[/li]
[li] Constrains:[/li]
[/ul] [ul]
[li]Primary Key[/li]
[li] Foreign Key(s)[/li]
[li] Unique[/li]
[li] Checks[/li]
[/ul]• Sequences – setting and re-setting
• Triggers
• Indexes
• Grant and Revoke privileges

Views

Database model
ER Diagram
Referential data integrity
Normalization (and de-normalization) of data[/blue]

---------------------
I do realize another huge portion of this class could be (should it be?) what not to do, what to avoid, common mistakes, costs (and nightmares) of doing it wrong, etc.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[johnherman]

Well, as someone who has worked in multiple places, there are two aspects. 1 - This is what Oracle features we are using in our environment and how we're using them. 2 - these are our company standards and best practices.

For instance, in part 1, Oracle only allows column names of up to 30 (unique) characters. Here at Andy company, we start all column names with a 3 byte prefix that tells what data type we use.

Or another example. Oracle allows columns to be NULL. When you join tables, NULL values are excluded from the join unless specifically coded. Here at Andy company, we don't allow NULL for any column that can be used in a join. If the true value isn't known, we us a surrogate key of ZZZZZZZZZ.

So, the two major subject areas can be covered 1 and then 2. Here's what Oracle can do, and then here's how we use Oracle at Andy company.

Or the subject area (column names, or NULL) can be covered and then within each subject, cover the two aspects. First, here's what Oracle says and does, and second, here's what we're doing here at Andy company as part of our standards and best practices.

Hope this helps.


==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[Andrzejek]

Thank you John,
Very good points. Just because Oracle allows creating tables with 200+ fields, no PK, no FKs, no constrains, no required values (all fields can be NULLs), fields like
Mngr1_ID (Character field, no numbers)
Mngr1_Name (Full Name)
Mngr2_ID
Mngr2_Name
Mngr3_ID
Mngr3_Name
Mngr4_ID
Mngr4_Name
Mngr5_ID
Mngr5_Name,
multiple locations of the same data (that changes) In many tables, calculated and concatenated fields, etc. – it does not mean you should do it. Imagine the nightmare.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.