Problem with database table design RRS feed

  • Question

  • I'm working on a C# project which is an advanced Eng to Eng dictionary.
    The Dictionary gets terms and returns information, like description, examples, synonyms, picture, audio pronunciation, phonetic and so on.
    So I designed 3 classes including Dictionary, Term & Description.
    I would like to create its database, and actually I've got confused! I don't know how to design my database and certainly its tables.
    My questions are:
    1: what should be the columns of my table (the fields of the classes or the classes themselves)?
    If the table’s columns should be the fields so what happens to my classes?!!
    2: If I want the table columns to be the classes so how can I do that using Microsoft SQL Server?
    I want to strictly stick to the OOP principals.

    Tuesday, August 18, 2015 4:17 PM


All replies

  • >I want to strictly stick to the OOP principals

    Check out Entity Framework Code-First.

    Code First to a New Database


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Hamed.R Friday, August 21, 2015 7:16 PM
    Tuesday, August 18, 2015 4:33 PM
  • This is a really broad question and generally deals with database normalization.


    The design is generally open for debate since the design changes depending on how the data is being used.  For most applications, 3NF is generally implemented which maximizes the efficiency of data writes over data reads.  Since database writes generally take longer than database reads, this is preferable for high volume applications since it requires less resources to update data.

    Keep in mind that tables generally grow vertically meaning rows.  I've seen a few cases where the database designer created columns for an attribute that can dynamically change over time.  For example, if you had a Family table, you should not create a column for each member of the family  such as ColumnDad, ColumnMom, or ColumnChild1.  It would be painful to have your application dynamically add columns as part of its normal insert operation.  Instead, it would be better to have a design that is data driven.  Given the same example, you may just have a MemberType column which identifies if it is the father, mother, or child.  This allows you to add an infinite number of child rows for that family without altering the table structure.

    There are also other database design issues that you should consider such as keys, indexes, partitions, and so on.  However, there are several articles on the web to help you out in those areas.

    Hope that helps.

    • Proposed as answer by Charlie Liao Friday, August 21, 2015 5:33 AM
    Tuesday, August 18, 2015 4:41 PM
  • This would need a little more information for a detailed respons.

    In short, you need to look at the one to one and one to many relationships expressed in your objects. For example, let's say that the Term class is based on one term for the object instance. That term can have one language origin but can have multiple descriptions, each of which may have multiple valid pronunciations.
    You could then have the following:

    Table: tblTerm
    Column: TermID
    Column: Term
    Column: TermOrigin

    Table: tblTermDescription
    Column: TermID
    Column: DescriptionID (either unique to the term or unique overall)
    Column: Description

    Table: tblPronunciation
    Column: TermID
    Column: DescriptionID
    Column: PronunciationID
    Column: PronunciationDescription
    Column: PronunciationAudio

    This is only to demonstrate the general concept of isolating the one to one and one to many relationships. When you have one to many relationships, you generally need a table unique to the "One" level, with contains a unique identifier and any attributes of that identifier which have at most one value. Then create the "many" level, which has a column to refer to the "one" table, and contains each array element (in a form of object modeling terminology) in a different row.

    Hope this helps.

    Ernest Ostrander

    • Proposed as answer by Charlie Liao Friday, August 21, 2015 5:33 AM
    Tuesday, August 18, 2015 4:44 PM