We have the following data:
- Collections of items
- Individual items
These will go into the following tables:
ItemCollectionTable (Id, Name, OtherFields (desc, date, etc..))
ItemTable (Id, OtherFields)
When an item is added the user will be giving it a collection straight away which is fine. We have a situation however, where users may want to take a whole collection and copy it into a new collection. To do this we've come up with these solutions:
Create a duplicate for every item in the Item table with the original scenario, only changing the PK and Scenario FK.
Problem obviously being duplicating data
Use a linking table showing that an item is in two collections, only adding a new entry when data between the two differs.
The problem being the huge complexity of accessing the data but saves the duplicating issue above
Is there an obvious better solution or does the amount of data in the collection change the answer to go with? I'd be very grateful if there was another solution instead?