How should I model a group relation in the database and in OOP?

Question

I have instances of Item that I would like to have in groups ItemGroup. But if an Item is moved to another itemgroup, I would like to keep track of the change for accountability reasons. E.g. I need to know if an Item was in one ItemGroup in October and another in September.

How should I model this in the database? How should I model this in my classes, OOP? I can see a few different solutions, but they are all complicated in some way so I don't know how to implement it.

Either I could go with three tables, Item ItemGroup GroupRelation and keep an timestamp on the GroupRelation. If the item information is updated I need to create a new item, and a new GroupRelation. If the Item changes group I have to create a new GroupRelation. And if the Group information is changed I have to create a new Group and a new GroupRelation. It is complex since I have to create multiple new objects on change.

Item
+----+---------+-----+------+
| id | item_nr | ean | name |
+----+---------+-----+------+

ItemGroup
+----+------------+-----+
| id | group_name | vat |
+----+------------+-----+

GroupRelation
+----+---------+----------+-----------+
| id | item_id | group_id | timestamp |
+----+---------+----------+-----------+

An alternative solution could be to have only two classes Item and ItemGroup but then I need to have a timestamp in both of them so I know when they are changed. If Group is updated, I have to update all Items that belongs to that group, so this is also complex.

Item
+----+---------+-----+------+----------+-----------+
| id | item_nr | ean | name | group_id | timestamp |
+----+---------+-----+------+----------+-----------+

ItemGroup
+----+------------+-----+-----------+
| id | group_name | vat | timestamp |
+----+------------+-----+-----------+

And there is probably other solutions, one is maybe to move old verions to another table. But then is it complex to search for data when both current and old data should be looked up. Or I could have a coulumn prev_id that link to the old version.

Is there anyone that have experience of similar datamodels and has any recommendation? Is there any best practices for this kind of problem?

Answer 1

I would go with your Item, ItemGroup and GroupRelation as a good normalized design with minimum duplication.

Your auditing requirement can be modeled with an additional table for each table you need to audit (for example: ItemAudit, ItemGroupAudit) which holds the fields you need to audit and a timestamp. You populate the audit table every time an auditable field changes.

That way, you have a historical record and don't encumber your day to day tables with historical data.

Answer 2

From your description I think there is a concept of a GroupRelationHistory which exists implicitly in your model. I would make it to an own entity like Item, ItemGroup and GroupRelation.

In the object world I would make Item to be aware of its GroupRelationHistory, meaning that an Item object has a reference to GroupRelationHistory object. Essentially GroupRelationHistory is just a list of zero, one or more GroupRelations.

Introducing this concept should be backed up by real business needs. Go to your customer (or some representative) and ask if the history is an entity of its own right and has some business value. If yes, think about this approach and refine it according to the business needs. Then think about the database model, which highly depends on the specific refinements. If no, then I would make the history concept a pure auditing feature like Oded suggested.