Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Table Design - SQL Query Question

Expand Messages
  • Arnie Rowland
    Good question Albert, And it s always a trade off about how far we will push normalization. There could easily be lookup tables for manufacturer, CPUType,
    Message 1 of 6 , Aug 10, 2006
    • 0 Attachment
      Good question Albert,

      And it's always a trade off about how far we will push normalization. There
      could easily be lookup tables for manufacturer, CPUType, MemorySize, etc.
      And a significant issue to consider is the skill of the developers/dba in
      re-constructing the 'whole' from the parts.

      An additional significant reason for normalization is to control data
      anomalies due to changes in 'lookup' tables. But I would suggest that it is
      highly unlikely that a computer would ever change color. Storage is so
      inexpensive that I often consider de-normalization for 'ease of use'.

      In fact, there 'should' be lookup tables (to use with dropdown lists) in
      order to constrain the number of colors, etc., that users will provide
      -otherwise there will eventually be a color such as 'blite' or 'whack'.

      The design consideration I had in mind when I asked about the db technology
      to be used is that with SQL Server 2005, there is a new datatype [xml] that
      may be ideal for this type of usage (name=value pairs.) It's not necessary
      to know (in advance) all of the name=value pairs that will eventually be
      stored, and the xml nodes are quite searchable. But again, that moves into
      'knowledge' and 'skill' issues.

      - Arnie Rowland

      "I am a great believer in luck, and I find that the harder I work, the more
      I have of it." - Thomas Jefferson (1743-1826)


      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com
      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
      Sent: Thursday, August 10, 2006 4:36 AM
      To: Arnie
      Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question

      Arnie, on the color question itself, as a design consideration I wonder
      of a look up table of color values might not be better. 1 = black, 2 =
      white, etc rather then having the 'redundant' black brown white etc ion
      the main table? Queries could then include the color word from the field
      if needed and the actual colors would be consistent:

      SELECT COUNT(colorField) as ColorCount
      FROM theTable
      WHERE colorField = "Color 1";

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
      > Sent: Wednesday, August 09, 2006 7:59 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
      >
      >
      > Which server product/version are you using?
      >
      > - Arnie Rowland
      >
      > "I am a great believer in luck, and I find that the harder I
      > work, the more I have of it." - Thomas Jefferson (1743-1826)
      >
      >
      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Modha Kumar
      > Sent: Wednesday, August 09, 2006 4:15 PM
      > To: Arnie
      > Subject: [SQLQueriesNoCode] Table Design - SQL Query Question
      >
      > Hi All
      >
      > I have to store a machine name and some config params (Name - Value
      > pairs).
      >
      > Ex:
      > Machine1 CPU - 1GB, Color - Black, Make-Dell
      > Machine2 CPU - 2GB Color- Brown Make -HP
      > etc
      > I have to store Color as well as Black because I am not sure how many
      > such name value pairs I have to store.
      >
      > Now I need to query such that , give me all machines for which color
      > is black. It should return me Machine1,...
      >
      > I am just looking for an idea how to do it.
      >
      > Please let me know.
      >
      > Thanks
      > bib





      Yahoo! Groups Links











      Disclaimer - August 10, 2006
      This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
      This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


      [Non-text portions of this message have been removed]
    • Frank Allen
      Another Solution, but not sure about the performance (1) Table :: Machine ... Id, Name, Model, related attributes 1 ABC 123 ... 2 XYZ
      Message 2 of 6 , Aug 11, 2006
      • 0 Attachment
        Another Solution, but not sure about the performance

        (1)
        Table :: Machine
        -----------------------------------------------------
        Id, Name, Model, related attributes
        1 ABC 123 ...
        2 XYZ 789 ...
        .
        ..


        (2)
        Table :: MachineParts
        ------------------------------------------------------
        MachineId, Attribute, Value
        1 Color Black
        1 Color Green
        1 HD 80GB
        1 RAM 1GB
        2 Color White
        2 HD 160GB
        2 RAM 2.5GB

        Declare
        @Attribute varchar(256)

        --Set @Attribute = 'RAM'
        --Set @Attribute = 'HD'
        --Set @Attribute = NULL
        Set @Attribute = 'Color'


        Select MP.MachineId, M.Name, M.Model,...,MP.Attribute, MP.Value
        From Machine M
        Inner Join MachineParts MP On M.Id = MP.MachineId
        Where MP.Attribute = IsNull(@Attribute,MP.Attribute)
        ---< Use Of @Variable to get a multi purpose query)


        John Warner <john@...> wrote:
        Arnie, on the color question itself, as a design consideration I wonder
        of a look up table of color values might not be better. 1 = black, 2 =
        white, etc rather then having the 'redundant' black brown white etc ion
        the main table? Queries could then include the color word from the field
        if needed and the actual colors would be consistent:

        SELECT COUNT(colorField) as ColorCount
        FROM theTable
        WHERE colorField = "Color 1";

        John Warner

        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
        > Sent: Wednesday, August 09, 2006 7:59 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
        >
        >
        > Which server product/version are you using?
        >
        > - Arnie Rowland
        >
        > "I am a great believer in luck, and I find that the harder I
        > work, the more I have of it." - Thomas Jefferson (1743-1826)
        >
        >
        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Modha Kumar
        > Sent: Wednesday, August 09, 2006 4:15 PM
        > To: Arnie
        > Subject: [SQLQueriesNoCode] Table Design - SQL Query Question
        >
        > Hi All
        >
        > I have to store a machine name and some config params (Name - Value
        > pairs).
        >
        > Ex:
        > Machine1 CPU - 1GB, Color - Black, Make-Dell
        > Machine2 CPU - 2GB Color- Brown Make -HP
        > etc
        > I have to store Color as well as Black because I am not sure how many
        > such name value pairs I have to store.
        >
        > Now I need to query such that , give me all machines for which color
        > is black. It should return me Machine1,...
        >
        > I am just looking for an idea how to do it.
        >
        > Please let me know.
        >
        > Thanks
        > bib





        __________________________________________________
        Do You Yahoo!?
        Tired of spam? Yahoo! Mail has the best spam protection around
        http://mail.yahoo.com

        [Non-text portions of this message have been removed]
      • John Warner
        Your example points out an interesting problem with your design, the impossible has been allowed to happen, you have a machine (1) that is two colors at
        Message 3 of 6 , Aug 11, 2006
        • 0 Attachment
          Your example points out an interesting problem with your design, the
          'impossible' has been allowed to happen, you have a machine (1) that is
          two colors at once.

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Frank Allen
          > Sent: Friday, August 11, 2006 4:58 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
          >
          >
          > Another Solution, but not sure about the performance
          >
          > (1)
          > Table :: Machine
          > -----------------------------------------------------
          > Id, Name, Model, related attributes
          > 1 ABC 123 ...
          > 2 XYZ 789 ...
          > .
          > ..
          >
          >
          > (2)
          > Table :: MachineParts
          > ------------------------------------------------------
          > MachineId, Attribute, Value
          > 1 Color Black
          > 1 Color Green
          > 1 HD 80GB
          > 1 RAM 1GB
          > 2 Color White
          > 2 HD 160GB
          > 2 RAM 2.5GB
          >
          > Declare
          > @Attribute varchar(256)
          >
          > --Set @Attribute = 'RAM'
          > --Set @Attribute = 'HD'
          > --Set @Attribute = NULL
          > Set @Attribute = 'Color'
          >
          >
          > Select MP.MachineId, M.Name, M.Model,...,MP.Attribute, MP.Value
          > From Machine M
          > Inner Join MachineParts MP On M.Id = MP.MachineId
          > Where MP.Attribute = IsNull(@Attribute,MP.Attribute)
          > ---< Use Of @Variable to get a multi purpose query)
          >
          >
          > John Warner <john@...> wrote:
          > Arnie, on the color question itself, as a design
          > consideration I wonder of a look up table of color values
          > might not be better. 1 = black, 2 = white, etc rather then
          > having the 'redundant' black brown white etc ion the main
          > table? Queries could then include the color word from the
          > field if needed and the actual colors would be consistent:
          >
          > SELECT COUNT(colorField) as ColorCount
          > FROM theTable
          > WHERE colorField = "Color 1";
          >
          > John Warner
          >
          > > -----Original Message-----
          > > From: SQLQueriesNoCode@yahoogroups.com
          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
          > > Sent: Wednesday, August 09, 2006 7:59 PM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
          > >
          > >
          > > Which server product/version are you using?
          > >
          > > - Arnie Rowland
          > >
          > > "I am a great believer in luck, and I find that the harder I
          > > work, the more I have of it." - Thomas Jefferson (1743-1826)
          > >
          > >
          > > -----Original Message-----
          > > From: SQLQueriesNoCode@yahoogroups.com
          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Modha Kumar
          > > Sent: Wednesday, August 09, 2006 4:15 PM
          > > To: Arnie
          > > Subject: [SQLQueriesNoCode] Table Design - SQL Query Question
          > >
          > > Hi All
          > >
          > > I have to store a machine name and some config params (Name - Value
          > > pairs).
          > >
          > > Ex:
          > > Machine1 CPU - 1GB, Color - Black, Make-Dell
          > > Machine2 CPU - 2GB Color- Brown Make -HP
          > > etc
          > > I have to store Color as well as Black because I am not
          > sure how many
          > > such name value pairs I have to store.
          > >
          > > Now I need to query such that , give me all machines for which color
          > > is black. It should return me Machine1,...
          > >
          > > I am just looking for an idea how to do it.
          > >
          > > Please let me know.
          > >
          > > Thanks
          > > bib
        Your message has been successfully submitted and would be delivered to recipients shortly.