none
O/R Mapping - Creating a hierarchy within the objects to use dot notation RRS feed

  • Question

  •  

    Hi,

     

    I'm working on a project requiring some basic O/R mapping and I'm wondering what the best/recommended practice is for establishing a hierarchy and grouping of values that have been mapped from the database so that i can use dot notation. An example of pseudo-data and how i want to map it will help explain:

     

    I have the following database table structure:

    Table: metric_values (this is the main table that holds the data that is mapped to objects)

     

    security_id      metric_id      value

    1                    12                34.23

    1                    13                32.21

    1                    14                54.56

    .

    .

    .

    2                    13                12.32

    2                    14                102.32

    2                    15                54.84

    2                    16                523.12

    .

    .

    .

     

     

    Table: metric_details

     

    id     metric_id     metric_type_id      metric_name

    1      13              1                          Revenue

    2      14              1                          Net Debt

    3      15              2                          Revenue

    4      16              2                          Net Debt

    .

    .

    .

     

    Table: metric_type_details

     

    id      metric_type_id      metric_type_name

    1       1                         Fiscal Year Data

    2       2                         Calendar Year Data

    .

    .

    .

     

    To map this, i have a Security Class with fields and properties for each metric_id. I'm mapping metric_ids to properties using reflection and attributes as described in this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2741007&SiteID=1).

     

    Within the Security object i will have fields for both metric_ids that have a metric_type_id of 1 (i.e. Fiscal Raw Data) and and metric_ids that have a metric_type_id of 2 (i.e. Calendar Raw Data).

     

    As the pseudo-data shows records with metric_ids 13 and 15 both correspond to Revenue but 13 has a metric_type_id of 1 and so is "Fiscal Year Data" whereas 15 has a metric_type_id of 2 so is "Calendar Year Data".

     

    What i want, is to create this separation in my object so that if i want "Fiscal Year Data" i can type:

     

    Code Snippet
    this.FiscalData.Revenue

     

    and if i want "Calendar Year Data" i can type:

     

    Code Snippet
    this.CalendarData.Revenue.

     

    Data which is then calculated within C# (and subsequently persisted to the database) will be stored in the object in a similar manner. i.e.

     

    Code Snippet
    this.CalculatedData.Whatever

     

    What i'm therefore needing help with, is creating this kind of hierarchy within my objects. I know it's possible to create FiscalData and CalendarData classes with the same fields and properties within each (obviously mapping to different underlying metric_ids) and then instantiate FiscalData and CalendarData objects within the Security class but that doesn't seem like a good way of doing this since, within the context of the data, a FiscalData or CalendarData object doesn't make make much sense form an OO point of view. Another alternative is nested classes but that pretty much the same thing.

     

    I also don't think collections within the Security Class for FiscalData and CalendarData would work since i would then have to type something like:

     

    Code Snippet

    this.FiscalData["Revenue"]

     

    which is not what i want. When i type "this.FiscalData." i want IntelliSense to give me a list of fields.

     

    So... any ideas how i achieve this.

     

    Many thanks.

     

    M.

    Tuesday, January 29, 2008 5:02 PM

All replies

  • It's not clear from your message what you're trying to do.  Are you trying to write your own Object/relational mapper?  Writting a good O/RM is very hard to do; I'd recommend using a third-party tool like LINQ-to-SQL, NHibernate, LLBLGen. etc.

     

    It's also not clear from your message where you get "Security" from, based on the database schema that you've described--which only seems to detail metric values.

     

    If you want to do O/RM, then you're looking at developing plain objects, which wouldn't publicly make available database IDs (that would be an implementation detail of the mapping, but would likely be stored in the individual objects).

     

    Tuesday, January 29, 2008 5:16 PM
    Moderator
  • "It's not clear from your message what you're trying to do.  Are you trying to write your own Object/relational mapper?  Writting a good O/RM is very hard to do; I'd recommend using a third-party tool like LINQ-to-SQL, NHibernate, LLBLGen. etc."

     

    No, i'm not writing my own O/R mapper, what i'm trying to achieve does not require the complexity involved with O/R mappers and their use. What i have is a normalised database with all the data of interest in the metric_values table. What i'm trying to achieve is map each metric_id to a field/property within my Security object (origin of which is further explained below), perform large number of calculations which would become inefficient and untidy in SQL and then store the calculated values back to the database.

     

    "It's also not clear from your message where you get "Security" from, based on the database schema that you've described--which only seems to detail metric values."

     

    The tables i've shown are only an excerpt of the database schema - The first column of the metric_values table is security_id and that's where Security comes from! There is of course a Securities table as well which holds things like security name, country of company, etc...

     

    "If you want to do O/RM, then you're looking at developing plain objects, which wouldn't publicly make available database IDs (that would be an implementation detail of the mapping, but would likely be stored in the individual objects)."

     

    I don't intend on making database IDs publicly available - I'm not sure from which part of the post that came across.

     

    Thanks

     

    M.

    Tuesday, January 29, 2008 5:29 PM
  • Ok, given the reply from Peter Ritchie i think presenting the problem within the scope that it arose is potentially detracting from what i'm actually asking so i'm reposting the question more simply and in isolation of the context in which it arose:

    Given a class:

    Code Snippet

    public class MyClass

    {   
        // fields
        private int field1
        private int field2
        private int field3
        private int field4

        // Corresponding Properties
        public int Field1
        {
        ...
        }

        public int Field2
        {
        ...
        }

        public int Field3
        {
        ...
        }

        public int Field4
        {
        ...
        }
    ...
    }


    is there a way of "grouping" the fields (and the corresponding properties) into lets say 2 groups, 'Group1' consisting of 'field1' and 'field2' and 'Group2' consisting of 'field3' and 'field4' so that instead of accessing the fields by typing

    Code Snippet

    this.Field1

    this.Field2
    this.Field3
    this.Field4


    i can type

    Code Snippet

    this.Group1.Field1

    this.Group1.Field2
    this.Group2.Field3
    this.Group2.Field4


    instead.

    I'd rather NOT do it by either:

    1. creating separate classes 'Group1' (with properties Field1 and Field2) and 'Group2' (with properties Field3 and Field4) and then instantiating them from within 'MyClass'

    OR

    2. Using collections since i would then have to use either this.collectionInstance[ i ] which is not very intuitive to read or this.collectionInstance["Field1"] which is subject to run time errors and does not provide any intellisense.

    Thanks

    M.
    Tuesday, January 29, 2008 11:48 PM