0

I am creating an app where businesses can get listed, and I would also like to add a facility where they can specify which countries they export to. So when a user searches for listings of a business in their country, they not only see the businesses who are located in their country, but businesses in other countries that may to export their services to their country too.

Here's my thoughts so far - any better solutions? I'm using Rails 3.2 and Postgres.

  • Country model, with fields for id, name, slug etc
  • Listing model, has a Postgres array field which stores the country ids that that business exports to

Then when I show listings for a country, I can just search for businesses that also have that country id in the country_export array.

But is this the most efficient way to do it? (I would use the postgres_ext gem to enable pg array support: https://github.com/dockyard/postgres_ext)

The other option I can think of is with checkboxes on the Listing model, so businesses can say whether they export worldwide, to the EU, to Asia, etc. Then add those as boolean fields on the country model. Downside to this is it won't be as accurate as specifically stating the countries... but I wouldn't mind the compromise if it is a more efficient way to go about it.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
A4J
  • 879
  • 10
  • 24
  • Have you ruled out mapping tables (company/country ID pairs) for some reason? It's an common solution to this problem that shouldn't require an extension, works with the more common indexing strategies, and would work with any RDBMS you migrate to in the future. – Matt Jan 12 '13 at 21:01
  • Hi Matt, I haven't ruled it out - but would that be less efficient? I know I probably shouldn't, but I worry (probably a bit too much) about performance and efficiency. – A4J Jan 12 '13 at 21:29
  • Is this what you meant Matt? http://stackoverflow.com/questions/14299259/rails-has-one-country-and-has-many-exportcountries (Any ideas on how to do it?) – A4J Jan 13 '13 at 04:35

1 Answers1

1

This is a classical n:m relationship between the two tables company and country, which is normally implemented by a table (like company_country) referring to the two primary keys of those tables.

See full code examples under these closely related questions:
How to implement a many-to-many relationship in PostgreSQL?
Many-to-many relation filter

For a list of companies in a certain country your query would be:

SELECT c.*
FROM   company_country cc
JOIN   company c USING (company_id)
WHERE  cc.country_id = ?

Relational database basics, SQL basics. With proper indexes, this is much faster than handling arrays.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hi Erwin, thanks - that's what I went for in the end (http://stackoverflow.com/questions/14299259/rails-has-one-country-and-has-many-exportcountries) so I'm pleased to learn it was the right path to take :) – A4J Jan 14 '13 at 23:57