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?

1 Answer 1


It's hard to say, without knowing how you plan to use this data.

For your first option, where you make physical copies of the records when the collection is copied, this might make sense if you plan to treat each instance of an item as distinct, and you anticipate that the details of the copied items will eventually diverge from where they were copied.

The second option makes more sense to me (but again, I don't have enough usage information). This option would let an item be referenced in multiple collections, but they are not true copies. I'd build the tables like this:

  id (PK)
  (other fields)

  id (PK)
  (other fields)

  id (PK)
  item_id (FK to items.id)
  collection_id (FK to collections.id)
  (other fields)

With this structure, you can easily copy an item to another collection by adding a new record to items_in_collections with the collection_id of the new collection. Accessing the data is not that complex. Here's how to get all items for a specified collection

select items.*
from items
inner join items_in_collections on items.id = items_in_collections.item_id
inner join collections on collections.id = items_in_collections.collection_id
where collections.id = $COLLECTION_ID --user specified collection ID
  • That's great, thank you for the detailed response! My only concern is that this will be hectic when an item gets changed, we'd have to look up to see if it is in another collection, if it is we'd then have to save a new item and change the row in items_in_collections accordingly. I suppose if our stored procedures work then we should have no difficulty afterwards
    – Alex
    Aug 30, 2013 at 7:31
  • @Alex: In that case you might be better off immediately creating a new record for the copied item. You'll have to do more analysis on your specific use-cases to see which will be better. Both solutions could be valid in different situations. Aug 30, 2013 at 13:41
  • Thanks, we've decided to go with the second option for the main reason that we are unsure how many users will use the feature, but we have a possibility of about a thousand collections being created containing a thousand records each which isn't nice to copy unless there's changes and it's a necessity
    – Alex
    Aug 30, 2013 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.