Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer
    
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 '13 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. –  FrustratedWithFormsDesigner Aug 30 '13 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 '13 at 13:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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