I have a software requirement, that needs to store the user that made the last modification to another object, for every object (assume that all objects are already mapped and into a BD).
So we have the ParamBase an object that holds the basic data for every object, this will be a super class, every other parameter in the system will Inherit this class, (Using @MappedSuperClass)
@MappedSuperclass
ParamBase {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Version
private Long version;
@Temporal(value = TemporalType.TIMESTAMP)
private Date timeStampLastModification;
@Column
private User userOfLastModification;
//constructors, getter setters, other stuff, etc
}
The User object, this will represent the user, also mapped into a BD, for now is just simple Object mapped as Entity, all other Parameters will have to store this User into a field.
@Entity
User {
//User stuff, cons, get, seters, etc
}
@Entity
Country extends ParamBase {
//This class extends ParamBase so will have a field to hold the
//userOfLastModification
}
In an EJB I have business logic that will work with several operations and objects, when in this case a parameter is updated, the userSession (assigned at login) will update the userOfLastModification field.
THE PROBLEM:
All of the objects will have a relation with User, so all other Objects have a mapped field userOfLastModification_id, this creates a massive dependency of all clases to User.
To give you an idea, just picture on an Entity Relation diagram ER, all tables related to one single User Table, it’s a mess!!!
THE REFACTOR!:
I think this model is wrong, I think breaks the equilibrium of software design I don’t think you need to create a relation between all the tables/object to one table (User) to accomplish this requirement, I will like some feedback or ides on how to:
1. Explain the design error, if there is one? (I always think that I can be wrong, and there’s no problem with that)
- Is there a better way to do this?
Thanks for your help!
From a database design perspective, there is nothing wrong whatsoever with tracking your users with a single table via a common reference field where applicable. You may want to not have a computed index on each and every table, but that’s an implementation detail rather than a design requirement.
On the Java side, however, your intuition is correct. You probably do not want each and every object to contain a full reference to user. Instead, include a standard field with a common naming convention, and treat the user reference simply as text. So, instead of
@MappedSuperclass
ParamBase {
private User userOfLastModification;
}
you would do something like:
@MappedSuperclass
ParamBase {
private String userOfLastModification;
}
You’d only spool up an actual User object when you need its various data points, such as if you’re translating a user’s internal key to a display name.
2
I like DougM’s answer about how to handle in Java (a string for the user who last updated the table record). The goal here is to manage this inside your app. However, I don’t think we have enough information here on the full-breadth of tracking updates in this system especially from the database perspective. Are you going to guarantee all changes to records are only going to occur in the app?
These are different requirements:
- Track every change a user makes to every table record.
- Track every change made to each record in the table regardless of who and how it was updated.
- Only allow users (in user table) to make changes to records and track the last one.
Your requirements seem to match #3. I don’t know enough about your app. That may be exactly what you need. In this case, I would enforce referential integrity between the user table and all the other tables. That way it doesn’t matter if your app or some other source makes data changes, they have to reference a user account record.
The solution to #1 is to just put some sort of user name into the “UpdateUser” field. Users could get deleted from the user table, but your app won’t care going forward. Lots of flexibility here. The client may or may not like that.
There are situations where #2 may be best. If you did an acquisition of Acme Company, you could import all these data and make the user = “Acme Import”. There may be some nightly job that runs that brings in data from an external source, so call it “Nightly Update”.
Of course performance and ease of managing the system will need to be considered. This isn’t so much of an answer of exactly how you should do it, but how you should go about finding a solution and some things to consider. You may need to clarify the requirements depending on the knowledge your client has about security, logging, and data management.
Refactoring with DougM approach to handle it with a Java String had the best result posible as only getters and setters where changed, with little amount of efford.
1