ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

An Intro to Database Design

Updated on November 15, 2013
Source

Every website, software application, or mobile app requires a functioning database to operate. Whether it contains information about POS data for a retailer or contact information for political campaign contributors and donors, such information and data must be organized and structured to be useful.

Database design follows the Database Development Life Cycle (DBLC) and is modeled on the Software Development Life Cycle (SDLC). The DBLC can contain several iterative steps depending on the situation. Though there are many approaches to designing a database, the overall approach is usually consistent throughout. The most important steps are delineated below to help you better understand the process, and make better decision regarding database design down the road.

Source

The Initial Study and Assessment

This is the first phase of the DBLC, similar in many ways to the planning phase of SDLC. An effective initial study phase helps determine the purpose of the database to help plan and prepare for subsequent steps as the project progresses. Specifications are detailed in the form of written documents containing client requirements and mockups which are then analyzed for accuracy and necessity. This phase consists of deep analysis of the data environment and ecosystem.

A thorough analysis of the client, its requirements, organizational structure, operating environment, and the nature of applications you are designing for is critical for success. Without a complete understanding of the various factors and variables at play, there can be a significant impact on the specifications and designing in later stages.

For instance, the database might have to be built for a small business or it could be for a complex corporate client. Both instances have profound differences and nuances to attend to. By having a well-developed mission statement it can serve as a guideline throughout the designing stage.

Prior to execution determine whether the company is an expanding one, whether it is dynamic in its requirements, or whether the nature of the application is transactional or analytical. Identify constraints such as where the company currently stands as regards to the current systems, methods and processes. Focus on issues surrounding the current situation, existing training opportunities, and any other limitations.

Define objectives and gather information such as what objectives the database is going to fulfill, what it is looking forward to store, what other kind of interactions is expected and any other scopes and boundaries that needs to be defined and analyzed.

Source

What Methods Are Involved with Database Design?

The first step, creating a conceptual database design allows for easy communication between developers and end-users and is a permanent description of database requirements. Following are the salient features of this phase:

  • It starts with building a model of the data that will be used based on the requirements of the system and is independent of all physical considerations such as what Database Management Systems (DBMS) would be used.
  • There are many techniques to create a conceptual model in the form of the Entity-Relationship (ER) model, Relational Model (RM), the table model etc. out of which the ER model is the most popular conceptual model for database design.
  • In the ER model, all the data that you would want to include in your database is listed as ‘entities’, their features labeled as ‘attributes’ and all the entities inter-related through ‘relationships’.
  • Once the entities and relationships are identified, next, attribute domains are established and primary and alternate key attributes are determined.
  • After this these written requirements are converted into E-R diagrams.
  • Thereon this conceptual model is validated and reviewed against user transactions.

Next step is the creation of a logical database design in which the conceptual model constructed in the previous step is now converted into an internal logical model. Following are the salient features of this phase:

  • Once the conceptual model is validated and reviewed, the data is then arranged into a logical structure (tables, indexes, and views) and mapped to specific storage objects supported by the targeted DBMS. (Image from: http://docs.oracle.com/)
  • If the DBMS is a relational database, data is mapped to tables (which store data in rows and columns) and if the DBMS is an object-oriented database then data is mapped to objects (used by Object Oriented Programming language).
  • Once all the relations have been mapped, data is then validated using normalization techniques (process of organizing data to minimize redundancy).
  • This logical model is then reviewed and validated with the help of user transactions.

The third and final step is creating a physical database design wherein the description for the implementation of the logical data model on secondary storage is created. Following are the salient features of this phase:

  • Now all the data is translated and loaded onto tables.
  • The base relations and the general constraints are designed using the SQL language.
  • All the transactions are analyzed.
  • The file organizations and indexes are chosen and the required disk space is configured.

Source

Database Implementation and Loading

Once the conceptual, logical and physical designs have been configured we come to the next step of implementation and loading wherein the physical realization of database design is done. Following are the salient features of this phase:

  • If there is a need for a new DBMS (like SQL Server or Access) then the DBMS software is selected after a careful consideration of the advantages and disadvantages of different DBMS packages available, their tools and features, and the hardware requirements.
  • The DBMS is installed on the machine and the database itself created in the DBMS.
  • Data is loaded onto the system to start of the DBMS.
  • The efforts done so far are finally implemented and your database is now up and running!

Testing

At this stage, the database is tested and fine-tuned against the specifications provided by the clients. Developers continuously try to make sure that the database is performing as planned. The systems are also evaluated in-situ to check for relations with other linked applications.

Maintenance and Evaluation

At this stage, the developers and designers try to fix problems associated with the systems which are sure to crop up time and again (no matter how perfectly designed!). They also suggest enhancements and identify new requirements.

Lynda Tutorial on Database Design

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)