locked
Relationships best practice - SQL or Datasets? RRS feed

  • Question

  • About Me; I don't have to much experience with developing my own software applications but have a love for development and continual improvement of systems, processes and self. As a hobby, and possibly for some income, I have decided to do my first application which is a Budgeting Database using Windows Forms in Visual Basics 2010 Express.

    Question; I have setup a SQL Server Compact 3.5 database (local) for my application and was wondering if it is better to setup relationships in the SQL database or in the DataSet? or do I need to do both? I plan on using my code to ensure data is correct before saving to the database. What is the norm for database design in a Windows Application?

    Apologies if this is in the wrong forum or if my question seams completely NOOB!

    Cheers T4


    Over & Out Big Buddy!

    Thursday, March 22, 2012 7:50 AM

Answers

  • If the Database is to be part of your solution, that is, to control the data as a "real" backend, it should be complete with relationships. That way, the data component is reliable on its own. The code is the front end, the UI, and each part of the work is compartmentalized.

    If the database is just a fancy flatfile, however, relationships can be left out. Though, both the front end and the back end together are one component, not two, because neither would be reliable without the other.


    • Edited by Brian TkatchEditor Thursday, March 22, 2012 12:40 PM
    • Marked as answer by T4Roy Friday, March 23, 2012 12:36 AM
    Thursday, March 22, 2012 12:38 PM
    Answerer
    • Marked as answer by T4Roy Friday, March 23, 2012 12:36 AM
    Thursday, March 22, 2012 3:06 PM

All replies

  • http://en.wikipedia.org/wiki/First_normal_form

    http://en.wikipedia.org/wiki/Second_normal_form

    http://en.wikipedia.org/wiki/Third_normal_form

     

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Thursday, March 22, 2012 7:56 AM
  • Thank you Uri,

    I have read a few of these article before but will go over them again. I am aware of Database design principles but want to know best practices for setting up the relationships; save in SQL, save in DataSet, using Code or a combination?

    Cheers T4


    Over & Out Big Buddy!

    Thursday, March 22, 2012 8:15 AM
  • What does it mean  "save in DataSet"? You should have PK and FK  to ensure data integrity  and more over to improve performance..

    http://www.youtube.com/watch?v=wMbPRHeYvMU


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 22, 2012 8:35 AM
  • Yes I have PKs and FKs in all tables that require it. I just finished reading 1NF, 2NF and 3NF and I believe all my tables conform to 3NF.

    "save in DataSet" I mean I am able to save relationships between tables within the DataSet.

    Should I create relationships between my tables in SQL or I should I create relationships between my tables in the DataSet. I think I need to do both so that is what I will do.

    Thanks for your feedback. T4


    Over & Out Big Buddy!

    Thursday, March 22, 2012 8:57 AM
  • No, otherwise you are performing lots of operation on the client  like delete\update\insert but usually you do not have to.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 22, 2012 9:23 AM
  • If the Database is to be part of your solution, that is, to control the data as a "real" backend, it should be complete with relationships. That way, the data component is reliable on its own. The code is the front end, the UI, and each part of the work is compartmentalized.

    If the database is just a fancy flatfile, however, relationships can be left out. Though, both the front end and the back end together are one component, not two, because neither would be reliable without the other.


    • Edited by Brian TkatchEditor Thursday, March 22, 2012 12:40 PM
    • Marked as answer by T4Roy Friday, March 23, 2012 12:36 AM
    Thursday, March 22, 2012 12:38 PM
    Answerer
    • Marked as answer by T4Roy Friday, March 23, 2012 12:36 AM
    Thursday, March 22, 2012 3:06 PM