I have a database table containing a list of systems relevant to the tool I am building, mostly in-house applications, or third-party systems we receive data from. This table is added to infrequently, approx every 2 months.
One of these systems is Windows itself, which is where we store our users’ LANs, and I now need to explicitly reference the ID relating to Windows to query for user name, team etc. I know it would be bad practice to embed the ID itself into the code, so my question is what would be the best way to avoid this? I’m assuming my options are:
- create a global constant representing this ID
- create a global enum containing all systems now
- create a global enum and add systems to it as & when they are required in the code
- retrieve the ID from the database based on system name
I appreciate this may seem a trivial question, but I am going to have many situations like this during the course of this build, and although we are in complete control of the database I would like to conform to best practice as far as possible.
Many thanks!
Short Answer: i would keep my data in the database. The last option is good one.
retrieve the ID from the database based on system name
In order to deal gracefully with IDs that you are referring, you may use Dictionaries to load all reference Id’s from database table(s) within your application. Depending on how frequently this Id’s may change, you may also cach this dictionary for a much better performance.
4
Retrieve the ID from the database based on system name
All your other options involve duplicating the database data in your code. Database and program design is usually a process of reducing data duplication, not increasing it.
If you were starting from scratch, and you didn’t expect a new system very often and there were only a few systems, maybe you would start out with an enum. When you got sick of recompiling and re-releasing the code every time you supported a new system, then you’d make a database table. But since you already have a database table, I can’t think of a reason not to use it.
I now need to explicitly reference the ID relating to Windows to query
for user name, team etc.
Simple:
select u.user_name, u.team, u.other_field
from user u
join system s on u.system_id = s.id
where s.name = 'Windows';
Or if the same user can be in multiple systems:
select u.user_name, u.team, u.other_field
from user u
join user_system_xref usx on usx.user_id = u.id
join system s on usx.system_id = s.id
where s.name = 'Windows';
Hmm… I see the “Object Oriented” tag in your post. I guess I’ll go just a little further and say that if you are using Hibernate, you might do something similar with a criteria query:
Crit<System> crit = Crit.create(System.class);
crit.add(Restrictions.eq("name", "Windows"));
System currSystem = crit.uniqueResult();
for (user : currSystem.getUsers()) {
System.out.println(user.getName());
System.out.println(user.getTeam().getName());
}
4
Ideally it would be best to query the database and store the data returned for future use. Creating enums for specific tables where you need the IDs duplicates data that you can get another way which should be avoided. That being said however, in many cases it makes more sense in practice to create enums with this data and update them as you update the information in those tables, because the effort involved in doing it the “correct” way only pays off if the tables change somewhat frequently. The effort involved to update an enum once a year or so by addiing an entry or two isn’t significant.
Don’t create anything as global though, create a constants class and expose it only where you need it. Also it would be best to go ahead and create the entire table as an enum now, this method can get really ugly if the enum gets out of sync with the database.
4
Data that changes should either be kept in a config file or in database. Data that need to be used in queries that represent business data should be in database. I would choose integrity over performance specially for small size data. I would go with option 4. Remember that changes to code requires version management. If this becomes frequent practice, you will get to spend unnecessary time in version management.