Database Best Practices

-
This is summarised from a book titled: Data Modeling
Some best practices are described below:


Database indexing
  • index foreign keys
  • index on columns with a lot of null values is useless
  • frequently updated columns should not be indexed
  • may not be a bad idea to use table scans for small tables (less than 1K rows)
  • short-rowed tables (few columns) should use index-organised table
  • b-tree index benefits performance if values are selective (distinct). The higher the index selectivity ratio, the better

Database views
  • perform better than SQL statements since views are pre-compiled (but Oracle does cache statements)
  • stored procedures perform better than views generally

Naming convention
  • Constraint: <TableName>_<Type>_<ColumnName> where may be PK, FK, UQ (unique constraint), CK (check constraint)
  • Index: <TableName>_<Type>_<ColumnName> where may be UX (unique index), IX (non-unique)
  • View: <EntityOrTableName>_VW


Code table structure

Many applications require the use of code tables. Instead of creating many different code tables, an alternative is to create a generic code table structure.

The structure is as follows:
  • CodeItem is used to store the code types
  • CodeItemValue is used to store the code key-value pairs



An example of its use follows:
  • codeItem.itemName = "Country"
  • codeItemValue.codeValue = "SG"; codeItemValue.codeValueDesc = "Singapore"
  • codeItemValue.codeValue = "MY"; codeItemValue.codeValueDesc = "Malaysia"

Comments

Post a Comment

Popular posts from this blog

Understanding ITIL Service Management the UML way…

-
Image
I originally had a great deal of difficult reconciling the differences amongst ITIL Change Management, Incident Management, and Problem Management. Having researched on the subjects and understanding them better now, I’ve tried to put the concepts together using a UML class diagram to capture my understanding and to keep it imprinted. Similarly, I wanted to differentiate the various categories of changes (Standard, Emergency, Normal) using an easy to understand & self-explanatory manner – a UML Activity Diagram!
Read more

How to depict (Professional-Looking) Logical Network Diagrams in Astah

-
Image
I have always had to create (professional-looking) UML diagrams using Visio-like icons. You’ve got to admit that showing a bunch of boring UML boxes hardly interests anyone less technical (like anyone whose designation has the words “manager” or “director”). This is actually possible in Astah! I’ve created a template for future projects that allows me to produce logical network diagrams that look like the following: To create this diagram, you need to do the following: Assign various icons to stereotypes. I created the following for my use: Create a new UML Deployment Diagram Every element in the diagram is created as a Node The nodes are subsequently assigned the appropriate <<stereotype>>. E.g. Internet is <<internet>>, WebZone FW is <<firewall>>, Web LB is <<loadBalancer>> Ensure the “Icon Notation” is set to “Customized Icon” for each node Instead of the boring boxes, the corresponding icons automatically gets displayed!
Read more