Database design

  • I want to know about Database design. If we think about 'DataBase Design', which would trigger first in our mind ?

    Pls dont tell 'do a search'. I am expecting some good answers from best experts.

    karthik

  • First questions that come to mind.

    What's the database for?

    What's going to be stored in it, for how long and why?

    Who's going to be using this and how?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi karthikeyan,

    when we think abount DB Design the 1st trigger comes in mind is DDL trigger on database.

    Trigger depends on the requirements.

  • "3rd Normal Form"

    Heh... Look it up 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • whcih one should comes to mind ?

    3 rd normal form

    (or)

    What's the database for?

    What's going to be stored in it, for how long and why?

    Who's going to be using this and how?

    (or)

    both

    karthik

  • absolutly correct karthikeyan, but beforeDB design we need to analyze do we really need a trigger. If yes the for what purpose we will desing it , trigger may be on table or on database (in Sql 2005 ) so it depends upon the user as well as on developer also.

    If suppose DB dev. want to maintain DDL log audit on database then will write a DB trigger.

    if want to maintain any table audit will create trigger on tables.

  • Ahbijit,

    Karthick doesn't need a "trigger"... what he said was...

    I want to know about Database design. If we think about 'DataBase Design', which would trigger first in our mind ?

    ... and where he said "which would trigger first in our mind?", he meant "What should be the first thing we take into consideration when designing a database?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 3 rd normal form

    (or)

    What's the database for?

    What's going to be stored in it, for how long and why?

    Who's going to be using this and how?

    (or)

    both

    Both... you cannot design a database in correct 3rd normal form without knowing the rest "what's going to be stored in it".

    The "how long" and "who's going to be using this and how" actually doesn't matter much. Whether it's for a GUI or Batch File processing doesn't matter because it should always be able to do both.

    If you're talking a "data mart", then that's something different. Frequently, they are both pre-summarized and somewhat "de-normalized" for performance reasons.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/26/2007)


    3 rd normal form

    (or)

    What's the database for?

    What's going to be stored in it, for how long and why?

    Who's going to be using this and how?

    (or)

    both

    Both... you cannot design a database in correct 3rd normal form without knowing the rest "what's going to be stored in it".

    The "how long" and "who's going to be using this and how" actually doesn't matter much. Whether it's for a GUI or Batch File processing doesn't matter because it should always be able to do both.

    If you're talking a "data mart", then that's something different. Frequently, they are both pre-summarized and somewhat "de-normalized" for performance reasons.

    3NF kind of entails that you know what the "database is for". Keep in mind that the single question "what's the DB for" represents a whole (often iterative) process describing in detail what the DB is for. You need to have an idea of the breadth and depth of the data you need to collect (let's call that the "universe"); you then need to organize your universe into items that naturally go together (let's call them "entities"), and then determine how your entities should relate to each other. Then, using 3NF, you then start looking at your ENTITIES, and try to make sure that THEY don't need to be broken up and organized into more granular entities, etc...

    This is not a single question anymore, as you can see. It's an intricate process, requiring involvement from both the business, dev and data areas in most cases.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeff Moden (11/26/2007)


    The "how long" and "who's going to be using this and how" actually doesn't matter much.

    I had a reason for writing those, but now I can't remember fully.

    Hmmm.... Think it had to do with need for archiving and growth planning (how long). OLTP or decision support (ie, do we need to consider storing pre-computed averages, selective denormalisation, etc)

    But those are more advanced concepts in DB design, and one of the biggest mistakes I see is people skipping intermediate steps to get to a final design.ie not bothering to normalise fully.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ok Let us assume i am going to design a database for a college or school.Basically i don't have good knowledge in DataBase Design. Although here i am going write how to design tables for a school, but i don't know whether it is correct.Asking your people to correct me.

    Table1:

    TableName - Admission

    PrimaryKey - StudId ( AutoGeneratedNumber - Assume StudId will be generated through front end logic)

    -------------------------------------------------------------------------------

    StudId StudentName FatherName/MomName Sex Nationality Religion Age Class Section Remarks

    --------------------------------------------------------------------------------

    Table2:

    TableName - Address

    ForeignKey - StudId depends Admission table StudId column

    ---------------------------------------------------------------------------

    StudId Address1 Address2 Address3 City Country Phone Mobile Email Comments

    ---------------------------------------------------------------------------

    Table3:

    FeeDetails

    ForeignKey - StudId depends Admission table StudId column

    ---------------------------------------------------------------------------

    StudId JoiningFee SpecialFee OtheerFee Remarks

    ---------------------------------------------------------------------------

    Just i designed some sample tables. is this called database design ?I know there are lot of things in Database design. So My question is,Is this called basic database design ?

    As your people said,

    1) Need to take a look into each and every table whether it satisfies 3NF.

    2) Need to decide DB Size

    3) Assume this DB would not be used by more than 15 peoples.

    Am i Correct ? If not please correct wherever i made mistakes.

    karthik

  • karthikeyan,

    There is one thing I don't understand. Your title is Senior Software Engineer and what kind of work you are doing so far. It seems you do not understand too much about database but you want to learn it in one day. You asked a lot of basic questions and if you are senior engineer, you should know them already.

    Database design is largely based on experiences, even you read all the database design books, without real life experiences, it is no use.

  • I was working on a software company that sold high education software.

    Table - Student

    StudentID - automatically generated

    Social Security number

    Student Name

    Birthday

    Gender

    Nationality

    Race

    Email Address

    Father Name

    Mother Name

    Table - StudentAddress

    AddressID - Auto generated

    StudentID - FK to Student table

    Address Type (Permanent, Billing....)

    Address1

    Address2

    City

    State/Provence

    Zipcode

    Country

    Telephone Number

    Table - StudentBilling

    StudentBillingID - Auto Generated

    StudentID - FK to Student Table

    Term (Fall 2007, Winter 2007....)

    Number of credit

    Tuition

    Other charges

    Table - StudentAdmission

    AdmissionID - Auto Generated

    StudentID - FK to Student Table

    Term Applied (Fall 2007, Fall 2008...)

    Student Application (Freshman, Transfer)

    Application Status (Accept, Declined, Waitlist)

    Transcript_Recieved (Yes/No)

    Reference_letter_received (Yes/No)

    my 2 cents

  • Karthik,

    No... that's what a lot of folks do and it's not database design. For starters, each table should have a primary key (as well as having a clustered index which may not show on such plans). You've mixed the "Next of Kin" information with the student information on the same row and called it by the wrong name "Admission". You mixed class information with student information on the same row, as well. I see no sign of an audit plan nor planned indexes for performance. I see no generic data type information nor planned data constraints. I see only a minor attempt at a naming convention. I see no "User" table to control access via a gui and I see no table for Class information. Most of the tables in your example are pretty much denormalized and that's not good. I could go on, but I think you get the idea.

    Database design isn't just throwing together a handfull of databases and calling it done. Sure, you might be able to get away with it for a while... but in the face of scalability, performance, and reporting requirements, such nonchalant attempts at design will simply not stand the test of time.

    So, where do you begin learning about proper database design? Just like I said... the first thing you should learn is about "normalization". Look that word up in Books Online and study it. Do it now before you learn the wrong way to do things... it'll be tougher to "unlearn what you have learnt".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Thanks for your proper explanation.

    As you told, i have started to study about 'Normalization'.

    karthik

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply