Should I duplicate the data or have linking table?

Question

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?

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:

items
-----
  id (PK)
  (other fields)

collections
-----------
  id (PK)
  (other fields)

items_in_collections
--------------------
  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