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

Database Design 101

Database Design 101

(OP)
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.
---------------------
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:
  • Types
  • Size
  • NULLs
  • Default values
  • Constrains:
  • Primary Key
  • Foreign Key(s)
  • Unique
  • Checks
• 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


---------------------
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.

RE: Database Design 101

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)


RE: Database Design 101

(OP)
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.

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