none
All Tables in single dataset vs multiple datasets. RRS feed

  • Question

  • Hi

    Is it better to use one dataset for my entire application and include all tables in the dataset or to use multiple datasets and only include related tables in each dataset?Regards

    Dimitris

    Friday, November 26, 2010 1:08 PM

Answers

  • Like any other anwser: It depends :)

    Each DataSet should contain only tables that represent one logical unit and it's related data. In some cases you might run into dilemma whether to put non-related tables into one DataSet. Good example of that are shared code-lists. In this case the answer is yes, you should put them toggether, but make sure that every module in your application uses nearly all tables from this DataSet, otherwise it's completly usless to use this approach.

    Here's a basic example.

    Let's say I'd like to create a paint application. This application also allows me to create a document from my paintings. Imagine this application as two-moduled application. Painter and Document Designer. Now we know, that some data (for example Colors, Tools, ...) will be shared between those two modules, so we create a DataSet (for example CommonData) which will contatin only those tables that are common to those modules. Now, each module itself should have it's own DataSet which will contain only data that is relevant to a module.

    Using this approach brings along many benefits:

    1. Clear structure - easier maintanance
    2. Import/export only logical units instead of all data
    3. Better performance and control over data

    ...and many others.


    Regards,
    Danijel

    Blog, Twitter
    • Proposed as answer by BonnieBMVP Sunday, November 28, 2010 1:03 AM
    • Marked as answer by liurong luo Friday, December 3, 2010 1:49 AM
    Saturday, November 27, 2010 9:41 AM

All replies

  • hi, 

    i think you use from typed dataset,

    in Porfessional programming, you fetch only some records from DataBase when ever you need for them,

    and each time you create a DataSet object and  add related table to that dataSet pro grammatically   and after your work is finished with dataset ,

    you release allocated memory by dataSet,

    there for that is not a problem in programming.


    .Net adventurer

    If (a post answers your question) {please click " Mark As Answer" and "Mark as Helpful" on that post} 

    Friday, November 26, 2010 3:44 PM
  • Like any other anwser: It depends :)

    Each DataSet should contain only tables that represent one logical unit and it's related data. In some cases you might run into dilemma whether to put non-related tables into one DataSet. Good example of that are shared code-lists. In this case the answer is yes, you should put them toggether, but make sure that every module in your application uses nearly all tables from this DataSet, otherwise it's completly usless to use this approach.

    Here's a basic example.

    Let's say I'd like to create a paint application. This application also allows me to create a document from my paintings. Imagine this application as two-moduled application. Painter and Document Designer. Now we know, that some data (for example Colors, Tools, ...) will be shared between those two modules, so we create a DataSet (for example CommonData) which will contatin only those tables that are common to those modules. Now, each module itself should have it's own DataSet which will contain only data that is relevant to a module.

    Using this approach brings along many benefits:

    1. Clear structure - easier maintanance
    2. Import/export only logical units instead of all data
    3. Better performance and control over data

    ...and many others.


    Regards,
    Danijel

    Blog, Twitter
    • Proposed as answer by BonnieBMVP Sunday, November 28, 2010 1:03 AM
    • Marked as answer by liurong luo Friday, December 3, 2010 1:49 AM
    Saturday, November 27, 2010 9:41 AM
  • Not quite grasping this answer.

    If I have fifty forms and say 1 form uses 3 tables out of say the complete database structure of 100 tables. Do you create 1 dataset with all tables and relationships or do you create individual datasets that cope with each forms logic?

    Bringing me onto can you combine datasets and use them in situations?

     

    Regards

    Skittery

    Friday, December 3, 2010 8:09 AM
  • Hi Skittery,

    There's no general rule how to design a DataSet. You should be staring to have one DataSet for one logical unit. It's up to you, how you define a logical unit. Be it a dataset with 100 tables or just 2.

    In your case, you could create one dataset for a few forms (for instance: master-detail editing), but absolutely not one for all.

    Imagine your project written in just one class....a total mess huh? It's the same with dataset. If you know what SoC (separation of concerns - http://en.wikipedia.org/wiki/Separation_of_concerns) means, then you know what I'm talking about.

    To make sure you get a clear picture, I'll try to explain how we separated tables into datasets on a large project. The project had a few major modules, but only 3 are relevant for this explanation and I'll use a simplified version of it. First module was used to design blueprints, second to design work processes and the third to plan the production.

    A brief explanation of how this software works: To design a work process you have to have a bluprint, but blueprint does't necessarily have a work process (company that uses this software makes a lot of blueprints but not all of them goes in production). Once you're done with a process, you can plan the production.

    How this looks like in visual? We have 4 forms:

    • blueprint designer
    • list of blueprints
    • process designer
    • production planner form

    ...and a few tables separated in a few datasets:

    1. DS_Blueprint
      - Blueprint (Id, Name, ...) - master table
      - Item (Id, Name, ColorId, ...) - child table
      - Connection (Id, ItemId1, ItemId2) - child table
    2. DS_BlueprintList
      - Blueprint (Code, Name) - same table as above but with only relevant data (to display in data grid)
    3. DS_Process
      - Process (Id, Name, ..., BlueprintId) - master table
      - Item (Id, Name, ColorId, ...) - child table
      - Connection (Id, ItemId1, ItemId2) - child table
      - ProcessItem2BlueprintItem (Id, ProcessItemId, BlueprintItemId) - this table glues a process to a blueprint
    4. DS_Production
      - Production (Id, ProcessId, ...) - master table
      - Tool (Id, Name, ...) - child table
      - Tool2ProcessItem (Id, ToolId, ProcessItemId) - defines which process item will be made on which tool
      - Phase (Id, Name, ColorId, ...) - child table (phases, each one had a different color for easier planning)

    ...of course we had some code lists that were shared between Blueprint module and Process module:

    • DS_CommonData
      - Color (Id, Name, Value)
      - ItemUnit (Id, Name, Value)

    Here are reasons why we didn't put all tables in just one dataset:

    • on a list form we needed only data to display all blueprints (small amount of data transfered to a client)..loading all child tables for each blueprint is a complete nonsense
    • on a blueprint designer form we needed only relevant data for this module
    • on a process designer form, we simply used all code from Blueprints module and extend it to fit the Process shoes
    • in production planning we needed just data from process

    ... and a reason why we put some tables into the same dataset

    • table Color was used in all 3 modules
    • ItemUnit was used on Blueprints, Processes and another modules which was not mentioned here

    I hope I explained this deep enough.


    Regards,
    Danijel

    Blog, Twitter
    • Edited by Danijel MalikMVP Friday, December 3, 2010 10:12 PM fixed a lapsus
    • Proposed as answer by BonnieBMVP Saturday, December 4, 2010 5:46 PM
    Friday, December 3, 2010 7:28 PM