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

Re: Design question

Expand Messages
  • majidkhantalpur
    CountrySetup ... CountryID Country ProductSetup ... ProductID Product Available ... ProductID CountryID Available Table Contain the Information About Product
    Message 1 of 20 , Aug 9, 2009
      CountrySetup
      ------------------
      CountryID
      Country


      ProductSetup
      ------------------
      ProductID
      Product


      Available
      ----------------
      ProductID
      CountryID

      Available Table Contain the Information About Product in which country will be available.

      Source: www.geniusprogramers.com


      --- In SQLQueriesNoCode@yahoogroups.com, Dermot <paikkos@...> wrote:
      >
      > Hi,
      >
      > This question is probably OT for this list so if anyone knows a list
      > where my question might be better placed, please tell me. I'll post in
      > here in the hope someone can offer some guidance.
      >
      > I have a table of products/assets, about 300,000. Most of these
      > products can be sold world-wide but about 40% are restricted in where
      > they can be sold. EG:
      > product#1 can be sold in ALL countries;
      > product#2 can be sold in countries, x,y,z;
      > product#3 can be sold in ALL countries except a,b,c, x,y,z;
      >
      > Currently these restrictions are handling with the use of several bit
      > pattern fields on each product entry. It works, is terse but is hard
      > to code for. My question is, is there another way? I have been
      > contemplating a join table with productId, countryId x 243 (I think
      > there are 243 countries in the ISO list) but it would have to be 243
      > columns wide and about 400,000 rows long. That doesn't sound like good
      > design practise.
      >
      > Can anyone offer an alternative or an opinion on what might be the
      > best approach?
      > Thanx in advance,
      > Dp.
      >
    • Dermot
      ... Thanx for the feedback. I am not that well versed with some of the syntax. What is @ mean (@CountryID)? From my reading, tables should be long and thin. So
      Message 2 of 20 , Aug 9, 2009
        2009/8/8 Arnie Rowland <arnie@...>:
        >
        >
        > Adding to my previous note, I suggest moving the country filtering to
        > the JOIN condition rather than having it in the WHERE clause. (The Query
        > Processor most likely does that anyway, but you should write your code
        > to express the intent rather than leaving it to chance.
        >
        > SELECT {ColumnList}
        > FROM Products p
        > JOIN ProductCountry pc
        > ON p.productId = pc.productId
        > AND pc.CountryID = @CountryID
        > WHERE p.ProductID = @ProductID;
        >
        > Also note that using "SELECT * " is NOT considered a 'BEST' practice.
        > Identify the specific columns you wish to retrieve in the SELECT
        > statement.
        >

        Thanx for the feedback. I am not that well versed with some of the
        syntax. What is @ mean (@CountryID)?

        From my reading, tables should be long and thin. So the Products would
        not contain the metadata such as title and other specifics. Does that
        seem correct? Is there a rule of thumb about the degree of separation
        or the number of columns a table should have?

        Thanx,
        Dp.
      • John Warner
        Look up Normalize a Database or Normalization on Google; lots of examples and explanations. John Warner ... Query
        Message 3 of 20 , Aug 9, 2009
          Look up "Normalize a Database" or Normalization on Google; lots of
          examples and explanations.

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Dermot
          > Sent: Sunday, August 09, 2009 4:47 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: Re: [SQLQueriesNoCode] Design question
          >
          > 2009/8/8 Arnie Rowland <arnie@...>:
          > >
          > >
          > > Adding to my previous note, I suggest moving the country filtering to
          > > the JOIN condition rather than having it in the WHERE clause. (The
          Query
          > > Processor most likely does that anyway, but you should write your code
          > > to express the intent rather than leaving it to chance.
          > >
          > > SELECT {ColumnList}
          > > FROM Products p
          > > JOIN ProductCountry pc
          > > ON p.productId = pc.productId
          > > AND pc.CountryID = @CountryID
          > > WHERE p.ProductID = @ProductID;
          > >
          > > Also note that using "SELECT * " is NOT considered a 'BEST' practice.
          > > Identify the specific columns you wish to retrieve in the SELECT
          > > statement.
          > >
          >
          > Thanx for the feedback. I am not that well versed with some of the
          > syntax. What is @ mean (@CountryID)?
          >
          > From my reading, tables should be long and thin. So the Products would
          > not contain the metadata such as title and other specifics. Does that
          > seem correct? Is there a rule of thumb about the degree of separation
          > or the number of columns a table should have?
          >
          > Thanx,
          > Dp.
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • Arnie Rowland
          A table should contain all the data that is directly dependent on the Primary Key. If in the Product table, the primary key is ProductID (uniquely
          Message 4 of 20 , Aug 9, 2009
            A table 'should' contain all the data that is directly dependent on the
            Primary Key. If in the Product table, the primary key is ProductID
            (uniquely identifying a product), then all data specific to that product
            'should' be in the table. Under rare circumstances, it may be reasonable
            to have a second table with a one-to-one relationship with Product that
            contains product specific data. But that needs to be carefully reasoned.

            Then '@' identifies a variable in SQL code.


            Regards,

            Arnie Rowland, MVP (SQL Server)

            "Fortune favors the prepared mind." Louis Pasteur


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Dermot
            > Sent: Sunday, August 09, 2009 4:47 PM
            ..
            > Thanx for the feedback. I am not that well versed with some of the
            > syntax. What is @ mean (@CountryID)?
            >
            > From my reading, tables should be long and thin. So the Products would
            > not contain the metadata such as title and other specifics. Does that
            > seem correct? Is there a rule of thumb about the degree of separation
            > or the number of columns a table should have?
            ..



            Disclaimer - August 9, 2009
            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/
          • Noman Aftab
            Suppose Product A  not sold in any of the countries except  Brazil , will it be feasible to insert (243-1) records in Available table?  Best Wishes,
            Message 5 of 20 , Aug 9, 2009
              Suppose "Product A" not sold in any of the countries except "Brazil", will it be feasible to insert (243-1) records in "Available" table?
               Best Wishes,
              Noman Aftab

               
              http://www.jalandhari.qsh.eu




              ________________________________
              From: majidkhantalpur <majidkhantalpur@...>
              To: SQLQueriesNoCode@yahoogroups.com
              Sent: Sunday, 9 August, 2009 12:22:20
              Subject: [SQLQueriesNoCode] Re: Design question

               
              CountrySetup
              ------------ ------
              CountryID
              Country

              ProductSetup
              ------------ ------
              ProductID
              Product

              Available
              ------------ ----
              ProductID
              CountryID

              Available Table Contain the Information About Product in which country will be available.

              Source: www.geniusprogramer s.com

              --- In SQLQueriesNoCode@ yahoogroups. com, Dermot <paikkos@... > wrote:
              >
              > Hi,
              >
              > This question is probably OT for this list so if anyone knows a list
              > where my question might be better placed, please tell me. I'll post in
              > here in the hope someone can offer some guidance.
              >
              > I have a table of products/assets, about 300,000. Most of these
              > products can be sold world-wide but about 40% are restricted in where
              > they can be sold. EG:
              > product#1 can be sold in ALL countries;
              > product#2 can be sold in countries, x,y,z;
              > product#3 can be sold in ALL countries except a,b,c, x,y,z;
              >
              > Currently these restrictions are handling with the use of several bit
              > pattern fields on each product entry. It works, is terse but is hard
              > to code for. My question is, is there another way? I have been
              > contemplating a join table with productId, countryId x 243 (I think
              > there are 243 countries in the ISO list) but it would have to be 243
              > columns wide and about 400,000 rows long. That doesn't sound like good
              > design practise.
              >
              > Can anyone offer an alternative or an opinion on what might be the
              > best approach?
              > Thanx in advance,
              > Dp.
              >


               




              [Non-text portions of this message have been removed]
            • Arnie Rowland
              If Product A is only sold in Brazil, there will be only ONE row in the Available table. Regards, Arnie Rowland, MVP (SQL Server) Fortune favors the prepared
              Message 6 of 20 , Aug 9, 2009
                If "Product A" is only sold in Brazil, there will be only ONE row in the Available table.


                Regards,

                Arnie Rowland, MVP (SQL Server)

                "Fortune favors the prepared mind." Louis Pasteur




                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                Sent: Sunday, August 09, 2009 9:58 PM
                To: Arnie
                Subject: Re: [SQLQueriesNoCode] Re: Design question

                Suppose "Product A" not sold in any of the countries except "Brazil", will it be feasible to insert (243-1) records in "Available" table?
                Best Wishes,
                Noman Aftab


                http://www.jalandhari.qsh.eu




                ________________________________
                From: majidkhantalpur <majidkhantalpur@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Sunday, 9 August, 2009 12:22:20
                Subject: [SQLQueriesNoCode] Re: Design question


                CountrySetup
                ------------ ------
                CountryID
                Country

                ProductSetup
                ------------ ------
                ProductID
                Product

                Available
                ------------ ----
                ProductID
                CountryID

                Available Table Contain the Information About Product in which country will be available.

                Source: www.geniusprogramer s.com

                --- In SQLQueriesNoCode@ yahoogroups. com, Dermot <paikkos@... > wrote:
                >
                > Hi,
                >
                > This question is probably OT for this list so if anyone knows a list
                > where my question might be better placed, please tell me. I'll post in
                > here in the hope someone can offer some guidance.
                >
                > I have a table of products/assets, about 300,000. Most of these
                > products can be sold world-wide but about 40% are restricted in where
                > they can be sold. EG:
                > product#1 can be sold in ALL countries;
                > product#2 can be sold in countries, x,y,z;
                > product#3 can be sold in ALL countries except a,b,c, x,y,z;
                >
                > Currently these restrictions are handling with the use of several bit
                > pattern fields on each product entry. It works, is terse but is hard
                > to code for. My question is, is there another way? I have been
                > contemplating a join table with productId, countryId x 243 (I think
                > there are 243 countries in the ISO list) but it would have to be 243
                > columns wide and about 400,000 rows long. That doesn't sound like good
                > design practise.
                >
                > Can anyone offer an alternative or an opinion on what might be the
                > best approach?
                > Thanx in advance,
                > Dp.
                >







                [Non-text portions of this message have been removed]



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

                Yahoo! Groups Links







                Disclaimer - August 9, 2009
                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/
              • Noman Aftab
                Oops, actually I meant if it is sold in all countries except one country!  Best Wishes, Noman Aftab   http://www.jalandhari.qsh.eu
                Message 7 of 20 , Aug 9, 2009
                  Oops, actually I meant if it is sold in all countries except one country!
                   Best Wishes,
                  Noman Aftab

                   
                  http://www.jalandhari.qsh.eu




                  ________________________________
                  From: Arnie Rowland <arnie@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Monday, 10 August, 2009 10:22:10
                  Subject: RE: [SQLQueriesNoCode] Re: Design question

                   
                  If "Product A" is only sold in Brazil, there will be only ONE row in the Available table.

                  Regards,

                  Arnie Rowland, MVP (SQL Server)

                  "Fortune favors the prepared mind." Louis Pasteur

                  -----Original Message-----
                  From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab
                  Sent: Sunday, August 09, 2009 9:58 PM
                  To: Arnie
                  Subject: Re: [SQLQueriesNoCode] Re: Design question

                  Suppose "Product A" not sold in any of the countries except "Brazil", will it be feasible to insert (243-1) records in "Available" table?
                  Best Wishes,
                  Noman Aftab

                  http://www.jalandha ri.qsh.eu

                  ____________ _________ _________ __
                  From: majidkhantalpur <majidkhantalpur@ yahoo.com>
                  To: SQLQueriesNoCode@ yahoogroups. com
                  Sent: Sunday, 9 August, 2009 12:22:20
                  Subject: [SQLQueriesNoCode] Re: Design question

                  CountrySetup
                  ------------ ------
                  CountryID
                  Country

                  ProductSetup
                  ------------ ------
                  ProductID
                  Product

                  Available
                  ------------ ----
                  ProductID
                  CountryID

                  Available Table Contain the Information About Product in which country will be available.

                  Source: www.geniusprogramer s.com

                  --- In SQLQueriesNoCode@ yahoogroups. com, Dermot <paikkos@... > wrote:
                  >
                  > Hi,
                  >
                  > This question is probably OT for this list so if anyone knows a list
                  > where my question might be better placed, please tell me. I'll post in
                  > here in the hope someone can offer some guidance.
                  >
                  > I have a table of products/assets, about 300,000. Most of these
                  > products can be sold world-wide but about 40% are restricted in where
                  > they can be sold. EG:
                  > product#1 can be sold in ALL countries;
                  > product#2 can be sold in countries, x,y,z;
                  > product#3 can be sold in ALL countries except a,b,c, x,y,z;
                  >
                  > Currently these restrictions are handling with the use of several bit
                  > pattern fields on each product entry. It works, is terse but is hard
                  > to code for. My question is, is there another way? I have been
                  > contemplating a join table with productId, countryId x 243 (I think
                  > there are 243 countries in the ISO list) but it would have to be 243
                  > columns wide and about 400,000 rows long. That doesn't sound like good
                  > design practise.
                  >
                  > Can anyone offer an alternative or an opinion on what might be the
                  > best approach?
                  > Thanx in advance,
                  > Dp.
                  >

                  [Non-text portions of this message have been removed]

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

                  Yahoo! Groups Links

                  Disclaimer - August 9, 2009
                  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.policypa trol.com/

                   




                  [Non-text portions of this message have been removed]
                • Arnie Rowland
                  That should be fine. If all 300,000 products are sold on all 243 countries, the Available table could have 72.9 million rows. Good hardware, properly indexed
                  Message 8 of 20 , Aug 9, 2009
                    That should be fine. If all 300,000 products are sold on all 243 countries, the Available table could have 72.9 million rows.

                    Good hardware, properly indexed -not a problem.


                    Regards,

                    Arnie Rowland, MVP (SQL Server)

                    "Fortune favors the prepared mind." Louis Pasteur




                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                    Sent: Sunday, August 09, 2009 10:30 PM
                    To: Arnie
                    Subject: Re: [SQLQueriesNoCode] Re: Design question

                    Oops, actually I meant if it is sold in all countries except one country!
                     Best Wishes,
                    Noman Aftab

                     
                    http://www.jalandhari.qsh.eu




                    ________________________________
                    From: Arnie Rowland <arnie@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Monday, 10 August, 2009 10:22:10
                    Subject: RE: [SQLQueriesNoCode] Re: Design question

                     
                    If "Product A" is only sold in Brazil, there will be only ONE row in the Available table.

                    Regards,

                    Arnie Rowland, MVP (SQL Server)

                    "Fortune favors the prepared mind." Louis Pasteur

                    -----Original Message-----
                    From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab
                    Sent: Sunday, August 09, 2009 9:58 PM
                    To: Arnie
                    Subject: Re: [SQLQueriesNoCode] Re: Design question

                    Suppose "Product A" not sold in any of the countries except "Brazil", will it be feasible to insert (243-1) records in "Available" table?
                    Best Wishes,
                    Noman Aftab

                    http://www.jalandha ri.qsh.eu

                    ____________ _________ _________ __
                    From: majidkhantalpur <majidkhantalpur@ yahoo.com>
                    To: SQLQueriesNoCode@ yahoogroups. com
                    Sent: Sunday, 9 August, 2009 12:22:20
                    Subject: [SQLQueriesNoCode] Re: Design question

                    CountrySetup
                    ------------ ------
                    CountryID
                    Country

                    ProductSetup
                    ------------ ------
                    ProductID
                    Product

                    Available
                    ------------ ----
                    ProductID
                    CountryID

                    Available Table Contain the Information About Product in which country will be available.

                    Source: www.geniusprogramer s.com

                    --- In SQLQueriesNoCode@ yahoogroups. com, Dermot <paikkos@... > wrote:
                    >
                    > Hi,
                    >
                    > This question is probably OT for this list so if anyone knows a list
                    > where my question might be better placed, please tell me. I'll post in
                    > here in the hope someone can offer some guidance.
                    >
                    > I have a table of products/assets, about 300,000. Most of these
                    > products can be sold world-wide but about 40% are restricted in where
                    > they can be sold. EG:
                    > product#1 can be sold in ALL countries;
                    > product#2 can be sold in countries, x,y,z;
                    > product#3 can be sold in ALL countries except a,b,c, x,y,z;
                    >
                    > Currently these restrictions are handling with the use of several bit
                    > pattern fields on each product entry. It works, is terse but is hard
                    > to code for. My question is, is there another way? I have been
                    > contemplating a join table with productId, countryId x 243 (I think
                    > there are 243 countries in the ISO list) but it would have to be 243
                    > columns wide and about 400,000 rows long. That doesn't sound like good
                    > design practise.
                    >
                    > Can anyone offer an alternative or an opinion on what might be the
                    > best approach?
                    > Thanx in advance,
                    > Dp.
                    >

                    [Non-text portions of this message have been removed]

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

                    Yahoo! Groups Links

                    Disclaimer - August 9, 2009
                    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.policypa trol.com/

                     




                    [Non-text portions of this message have been removed]



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

                    Yahoo! Groups Links
                  • Joe Fawcett
                    If the designer is worried that this will be a problem, and in general products are sold in most countries, then they can always reverse the schema and have
                    Message 9 of 20 , Aug 10, 2009
                      If the designer is worried that this will be a problem, and in general
                      products are sold in most countries, then they can always reverse the schema
                      and have the forbidden countries in the CountryProduct table.



                      Joe



                      From: SQLQueriesNoCode@yahoogroups.com
                      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                      Sent: 10 August 2009 06:36
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: RE: [SQLQueriesNoCode] Re: Design question





                      That should be fine. If all 300,000 products are sold on all 243 countries,
                      the Available table could have 72.9 million rows.

                      Good hardware, properly indexed -not a problem.

                      Regards,

                      Arnie Rowland, MVP (SQL Server)

                      "Fortune favors the prepared mind." Louis Pasteur

                      -----Original Message-----
                      From: SQLQueriesNoCode@yahoogroups.com
                      <mailto:SQLQueriesNoCode%40yahoogroups.com>
                      [mailto:SQLQueriesNoCode@yahoogroups.com
                      <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of Noman Aftab
                      Sent: Sunday, August 09, 2009 10:30 PM
                      To: Arnie
                      Subject: Re: [SQLQueriesNoCode] Re: Design question

                      Oops, actually I meant if it is sold in all countries except one country!
                      Best Wishes,
                      Noman Aftab


                      http://www.jalandhari.qsh.eu

                      ________________________________
                      From: Arnie Rowland <arnie@... <mailto:arnie%401568.com> >
                      To: SQLQueriesNoCode@yahoogroups.com
                      <mailto:SQLQueriesNoCode%40yahoogroups.com>
                      Sent: Monday, 10 August, 2009 10:22:10
                      Subject: RE: [SQLQueriesNoCode] Re: Design question


                      If "Product A" is only sold in Brazil, there will be only ONE row in the
                      Available table.

                      Regards,

                      Arnie Rowland, MVP (SQL Server)

                      "Fortune favors the prepared mind." Louis Pasteur

                      -----Original Message-----
                      From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@
                      yahoogroups. com] On Behalf Of Noman Aftab
                      Sent: Sunday, August 09, 2009 9:58 PM
                      To: Arnie
                      Subject: Re: [SQLQueriesNoCode] Re: Design question

                      Suppose "Product A" not sold in any of the countries except "Brazil", will
                      it be feasible to insert (243-1) records in "Available" table?
                      Best Wishes,
                      Noman Aftab

                      http://www.jalandha ri.qsh.eu

                      ____________ _________ _________ __
                      From: majidkhantalpur <majidkhantalpur@ yahoo.com>
                      To: SQLQueriesNoCode@ yahoogroups. com
                      Sent: Sunday, 9 August, 2009 12:22:20
                      Subject: [SQLQueriesNoCode] Re: Design question

                      CountrySetup
                      ------------ ------
                      CountryID
                      Country

                      ProductSetup
                      ------------ ------
                      ProductID
                      Product

                      Available
                      ------------ ----
                      ProductID
                      CountryID

                      Available Table Contain the Information About Product in which country will
                      be available.

                      Source: www.geniusprogramer s.com

                      --- In SQLQueriesNoCode@ yahoogroups. com, Dermot <paikkos@... > wrote:
                      >
                      > Hi,
                      >
                      > This question is probably OT for this list so if anyone knows a list
                      > where my question might be better placed, please tell me. I'll post in
                      > here in the hope someone can offer some guidance.
                      >
                      > I have a table of products/assets, about 300,000. Most of these
                      > products can be sold world-wide but about 40% are restricted in where
                      > they can be sold. EG:
                      > product#1 can be sold in ALL countries;
                      > product#2 can be sold in countries, x,y,z;
                      > product#3 can be sold in ALL countries except a,b,c, x,y,z;
                      >
                      > Currently these restrictions are handling with the use of several bit
                      > pattern fields on each product entry. It works, is terse but is hard
                      > to code for. My question is, is there another way? I have been
                      > contemplating a join table with productId, countryId x 243 (I think
                      > there are 243 countries in the ISO list) but it would have to be 243
                      > columns wide and about 400,000 rows long. That doesn't sound like good
                      > design practise.
                      >
                      > Can anyone offer an alternative or an opinion on what might be the
                      > best approach?
                      > Thanx in advance,
                      > Dp.
                      >

                      [Non-text portions of this message have been removed]

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

                      Yahoo! Groups Links

                      Disclaimer - August 9, 2009
                      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.policypa trol.com/



                      [Non-text portions of this message have been removed]

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

                      Yahoo! Groups Links





                      [Non-text portions of this message have been removed]
                    • caj_y
                      Hi Dp., I am not that expert in these matters as others in this group. But I thought of giving it a try... As mentioned that most of the products will be sold
                      Message 10 of 20 , Nov 29, 2009
                        Hi Dp.,

                        I am not that expert in these matters as others in this group.
                        But I thought of giving it a try...

                        As mentioned that most of the products will be sold worldwide
                        i.e about 60%. If we use this information and add one column
                        in Product Table, say SELLING_SCOPE

                        having 4 possible values (except initial default NULL value)

                        A - Sold Worldwide (All Countries)
                        (This will save Link Table entries for 60% data)

                        M - Sold in Most countries (except few countries)

                        F - Sold in Few countries

                        N - Not sold (may be kept for Old stopped Products)
                        (No entries for these items in Link Table)

                        This will hardly have any effect on storage/perfomance of Product Table.

                        Now storing the actual links in a Link Table ProductCountry

                        for A
                        No need to add any row
                        for M
                        Just Countries where product Not Sold to be entered
                        with ofcourse a flag 'N" i.e. Not Sold in these Countries
                        for F
                        Just Countries where it is Sold is to be entered
                        with ofcourse a flag 'Y" i.e. Sold in these Countries
                        for N
                        No need to add any row
                        However while stopping existing product, deletion is required.

                        Now Joining ProductCountry Table with Product Table
                        as well as Country Table and using the FLAG as filter
                        will easily generate the required lists like

                        1) Country wise List of ALL Products Sold in Each Country
                        2) Country wise List of ALL Products NOT Sold in Each Country
                        3) Country wise List of number of Products Sold in Each Country
                        4) Country wise List of number of Products NOT Sold in Each Country

                        5) Product wise List of Countries where Each Product is Sold
                        6) Product wise List of Countries where Each Product is NOT Sold
                        7) Product wise List of number of Countries where Each Product is Sold
                        8) Product wise List of number of Countries where Each Product is NOT Sold
                        etc.......

                        I think, this kind of link table will hold minimum required rows.

                        Note: For determining the flag M or F, we can use our business
                        logic. OR else can decide upon a number 'N' which will be
                        criteria for flag.

                        i.e. Sold in < N countries, flag will be F
                        Sold in >= N countries, flag will be M

                        Thats all from my side....

                        I would sure like to know, whether this makes sense...

                        I would accept all experts views & criticism / appreciation alike.
                        It will help me improve n learn more.

                        Regards
                        Chetan


                        ===================================================================


                        --- In SQLQueriesNoCode@yahoogroups.com, Dermot <paikkos@...> wrote:
                        >
                        > Hi,
                        >
                        > This question is probably OT for this list so if anyone knows a list
                        > where my question might be better placed, please tell me. I'll post in
                        > here in the hope someone can offer some guidance.
                        >
                        > I have a table of products/assets, about 300,000. Most of these
                        > products can be sold world-wide but about 40% are restricted in where
                        > they can be sold. EG:
                        > product#1 can be sold in ALL countries;
                        > product#2 can be sold in countries, x,y,z;
                        > product#3 can be sold in ALL countries except a,b,c, x,y,z;
                        >
                        > Currently these restrictions are handling with the use of several bit
                        > pattern fields on each product entry. It works, is terse but is hard
                        > to code for. My question is, is there another way? I have been
                        > contemplating a join table with productId, countryId x 243 (I think
                        > there are 243 countries in the ISO list) but it would have to be 243
                        > columns wide and about 400,000 rows long. That doesn't sound like good
                        > design practise.
                        >
                        > Can anyone offer an alternative or an opinion on what might be the
                        > best approach?
                        > Thanx in advance,
                        > Dp.
                        >
                      • Dermot
                        2009/11/29 caj_y ... Hi ... I have started to make an attempt at this. The approach I have taken so far was the one suggested, where I have a
                        Message 11 of 20 , Nov 29, 2009
                          2009/11/29 caj_y <caj_y@...>
                          > Hi Dp.,

                          Hi

                          > I am not that expert in these matters as others in this group.
                          > But I thought of giving it a try...
                          >
                          > As mentioned that most of the products will be sold worldwide
                          > i.e about 60%. If we use this information and add one column
                          > in Product Table, say SELLING_SCOPE
                          >
                          > having 4 possible values (except initial default NULL value)
                          >
                          > A - Sold Worldwide (All Countries)
                          > (This will save Link Table entries for 60% data)
                          >
                          > M - Sold in Most countries (except few countries)
                          >
                          > F - Sold in Few countries
                          >
                          > N - Not sold (may be kept for Old stopped Products)
                          > (No entries for these items in Link Table)
                          >
                          > This will hardly have any effect on storage/perfomance of Product Table.
                          >
                          > Now storing the actual links in a Link Table ProductCountry
                          >
                          > for A
                          > No need to add any row
                          > for M
                          > Just Countries where product Not Sold to be entered
                          > with ofcourse a flag 'N" i.e. Not Sold in these Countries
                          > for F
                          > Just Countries where it is Sold is to be entered
                          > with ofcourse a flag 'Y" i.e. Sold in these Countries
                          > for N
                          > No need to add any row
                          > However while stopping existing product, deletion is required.
                          >
                          > Now Joining ProductCountry Table with Product Table
                          > as well as Country Table and using the FLAG as filter
                          > will easily generate the required lists like
                          >
                          > 1) Country wise List of ALL Products Sold in Each Country
                          > 2) Country wise List of ALL Products NOT Sold in Each Country
                          > 3) Country wise List of number of Products Sold in Each Country
                          > 4) Country wise List of number of Products NOT Sold in Each Country
                          >
                          > 5) Product wise List of Countries where Each Product is Sold
                          > 6) Product wise List of Countries where Each Product is NOT Sold
                          > 7) Product wise List of number of Countries where Each Product is Sold
                          > 8) Product wise List of number of Countries where Each Product is NOT Sold
                          > etc.......
                          >
                          > I think, this kind of link table will hold minimum required rows.
                          >
                          > Note: For determining the flag M or F, we can use our business
                          > logic. OR else can decide upon a number 'N' which will be
                          > criteria for flag.
                          >
                          > i.e. Sold in < N countries, flag will be F
                          > Sold in >= N countries, flag will be M
                          >
                          > Thats all from my side....
                          >
                          > I would sure like to know, whether this makes sense...
                          >
                          > I would accept all experts views & criticism / appreciation alike.
                          > It will help me improve n learn more.
                          >
                          > Regards
                          > Chetan

                          I have started to make an attempt at this. The approach I have taken
                          so far was the one suggested, where I have a join table that stores
                          the relationship between the product and the countries they cannot be
                          sold. So the table looks like this

                          prohibited_countries
                          ----------------------------------
                          product_id | country_id

                          Only products that are restricted will end up in this table.

                          The governing factor for me is the speed of the select queries. These
                          queries will be from the perspective of the product. A user has
                          perform a search that will result in a list of product_ids. I will
                          have to filter the list on the user's country id. I anticipate queries
                          such as "SELECT p.id FROM prohibited_countries AS p WHERE
                          p.product_id=? AND p.country_id=?". If the query returns a row, then
                          the product cannot be sold in that country.

                          I am not massively happy with this method because I think it's
                          counter-intuitive. Moreover I will want to return details of the
                          product so I need a join, something along the lines of "SELECT p.*
                          FROM products AS p JOIN prohibited_countries AS pc ON
                          p.product_id=pc.product_id WHERE pc.product_id=? AND pc.country_id NOT
                          =?". But this is not ideal either because NOT queries are not very
                          efficient.

                          There are some counties where all products can be sold. For those
                          countries, I will not not preform the query.

                          I think the fastest method for my needs would be a bit-pattern within
                          the product table. The problem with that is that it's very difficult
                          to programme and interpret. What I really want to do but I don't have
                          the ability, is to compare performance of the JOIN/LINK table method
                          with a bit pattern. I suspect that a query like "SELECT * FROM
                          products WHERE product_id=? && prohibited_countries=000001000;" would
                          out-perform the JOIN table.

                          Thanks for the interest. I'd be glad to hear if any one has an opinion
                          on my bit-pattern vs join table theory or can suggest some form of
                          bench-marking tool I might use.
                          Dp.
                        Your message has been successfully submitted and would be delivered to recipients shortly.