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

Re: [SQLQueriesNoCode] Design question

Expand Messages
  • 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 1 of 20 , Aug 9, 2009
    • 0 Attachment
      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 2 of 20 , Aug 9, 2009
      • 0 Attachment
        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 3 of 20 , Aug 9, 2009
        • 0 Attachment
          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 4 of 20 , Aug 9, 2009
          • 0 Attachment
            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 5 of 20 , Aug 9, 2009
            • 0 Attachment
              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 6 of 20 , Aug 9, 2009
              • 0 Attachment
                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 7 of 20 , Aug 9, 2009
                • 0 Attachment
                  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 8 of 20 , Aug 10, 2009
                  • 0 Attachment
                    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 9 of 20 , Nov 29, 2009
                    • 0 Attachment
                      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 10 of 20 , Nov 29, 2009
                      • 0 Attachment
                        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.