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

OR

• 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?

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

OR

• 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?

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

OR

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?

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

OR

• 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?