I'm working on a project where we have to import data from a client's old system and it does not quite match the schema of the objects we're choosing in Salesforce.
One example is that the table we're importing into Case has a many-to-many relationship with the table we're importing into Asset (i.e. a customer called about a problem with several of our products that they own). Case has an existing lookup to Asset, however some of the records point to multiple Assets. Granted this is only a minority of Cases: less than 0.5% of them.
So one strategy could be, for every Case with multiple Assets, create a parent Case with child Cases and each child points to one Asset. The other content of the Case would just be duplicated on import.
Another strategy is a junction table, but now we have to grapple with the fact that there's already a lookup relationship. The majority of Cases we import only have one Asset, so do we fill in the standard Asset field for those and use the junction table only for the multi-Asset Cases? Or should we not use the standard field at all and let the junction table also include entries tying all single-Asset Cases together as well?
I've been thinking about this way too much so I thought I'd ask the crowd for some advice. Thanks :)