Modeling shmodelling!

Hi!

I'm designing a Rails app that imports data from external sources, and
would really appreciate some suggestions on the best way to achieve
the following:

Two models: Products & Categories, where the product details are
imported from an external source on an ongoing basis, and have named
categories that may not always match the categories listed in the
Categories model, for example there might be a "widget" of external
category "things", that needs to be in internal category "stuff". and
another where the internal and external categories have the same name
(more the latter than the former).

The external and internal category names stay constant for the most
part (although each source will have some variation on the names), but
the products constantly change. Some sources will have multiple
categories that map to one category in the Category model, but
probably not the reverse.

How would you model that relationship?

I've done it one way, which works, but isn't efficient either to
maintain or from a SQL perspective, and have an idea for another way,
but don't know enough about the knock on effects on Rails or SQL of
one approach over another. Rather than list them here, I'm open to any
and all suggestions & advice!

Many thanks,
Matt.

Some additional context: There are maybe 70-100 categories per data-
source, a hundred or so data sources initially, and unlimited products
per data-source (which expire after a period of time).

At some point internal categories will need to be i18n'd (?! :-), and
external categories could be in any language.

MattB wrote:

Two models: Products & Categories, where the product details are
imported from an external source on an ongoing basis, and have named
categories that may not always match the categories listed in the
Categories model, for example there might be a "widget" of external
category "things", that needs to be in internal category "stuff". and
another where the internal and external categories have the same name
(more the latter than the former).

Sounds like you need a category map model (external to internal) for
those exception cases. When internal and external categories are the
same, there would not be any entry for the external category (although
you might include those for code simplicity); but definitely records for
the exceptions where you need to map external category "thing" to
internal category "stuff".

...

At some point internal categories will need to be i18n'd (?! :-), and
external categories could be in any language.

I assume that the 'catmap' model could handle those requirements.