Basics of Data Modeling and Designing – Simple.

This article covers basics of database modeling and designing based on information flow from customer’s imagination of a software application to a physical database design.

I will use titles and images rather a long text and advise you to ask questions on any of the term which is not very clear. I am not saying that I am a guru, but I will try to explain certain rules and methods to keep in mind before and during designing a database which will make designing easy and database efficient.

First, we will understand difference between modeling and designing followed by database modeling process.

Fig 0.1 Data Modeling Process

Terms made simple: –

• Data Model – Is an abstract structure of elements, related to the properties of the real-world entities.
• Data Design – Detailed data model which represents business functionality.
• Data Models – A data model is a descriptive diagram which defines how to arrange data to serve a definite purpose.
  1. Conceptual – Also called as domain model, comprises with high-level business thoughts consuming terminology that business front-runners practice, which is easily understood by stakeholders and used by business analyst.
     
     Fig 0.2 Conceptual Model

1. Enterprise -: This model does not classify data into domains. This model represents single definition of data.
   
   Fig 0.3 Enterprise Model

1. Logical – Fully-attributed data model which describes data requirements of business. A logical data model is a ruled detonation of the conceptual data model into all the entities and attributed.
   
   Fig 0.4 Logical Model

1. Physical – This is directly related to technology. This model is dependent on Version, Edition, implementation limitation and is made ready for use.
   
   Fig 0.5 Physical Design

• Database Management Systems – Concept designed for managing each objects of any sort of databases and RDBMS is specifically relational.
  1. OLAP – Deals with historical data for analysis, reporting and forecasting purpose. OLAP is characterized by relatively low volume of transactions and high volume of data.
  2. OLTP – OLTP is categorized to deal with huge number online transactions (INSERT, UPDATE, DELETE).
• Database Normalization – Normalization is the process of resourcefully organizing data in a database.
  1. First Normal Form (1NF): No repeating elements or groups of elements
  2. Second Normal Form (2NF): 1NF + No partial dependencies on a concatenated key
  3. Third Normal Form (3NF): 1NF + 2NF + No dependencies on non-key  attributes.
  4. BCNF: Stricter then 3NF. Every functional dependency/partial key (prime attribute) can only depend on a superkey.
• Database Model Schema – Database Schema = Data Model + Data + Meta Data (Detail about data)
  1. Star Schema: The one in which a central fact table is encircled by de-normalized dimensional tables.
  2. Snow Flake Schema: This schema is an enhancement of star schema by adding additional dimensions.
  3. Galaxy Schema: Galaxy schema contains many fact tables with some common dimensions (adapted dimensions). Its mainly looks like bee hives.
  4. Fact Constellation Schema: The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy.
  5. Entity Relationship: – Putting a Database in Third Normal Form
• Database Objects – SQL code to create Databases, Tables, Columns, Stored Procedures, Functions, Views, Keys etc.

There are no universally defined formulas to design database, selection of above elements depends on organisation, nature and volume of the business data.
Please comment or mail me for any question.

Thanks for reading 🙂

Keep reading , share your thoughts, experiences. Feel free to contact us to discuss more. If you have any suggestion / feedback / doubt, you are most welcome.

Stay tuned on Knowledge-Junction, will come up with more such articles / news
Local Business Directory, Search Engine Submission & SEO Tools