Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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!

share|improve this question

4 Answers 4

up vote 6 down vote accepted

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.

share|improve this answer
    
But wouldn't a dictionary technique just transfer the issue from hard coded IDs to hard coded string keys? It seems it all falls back to having something hard coded if you want to explicitly filter on a specific value. –  mike30 Sep 27 '12 at 18:12
    
@mike, you missed the point, read the answer that explains how dictionary needs to be loaded. –  Yusubov Sep 27 '12 at 18:13
    
He needs to filter with the explicit value "windows". If he loads the dictionary dynamically how does he identify the key which represents "windows". I understand the dictionary, but it seems to just push the issue down the road to the key-based access. –  mike30 Sep 27 '12 at 18:17
    
Whilst Mike's point is certainly valid, in this situation most of the systems we add will not need to be explicitly referenced in the code as they will just need to be available in comboboxes etc. When system-specific functionality is required then we can refer to the system by name from the Dictionary, which will be infrequent enough to not cause a problem. We're in control of the data, so it is our responsibility to ensure we don't decide to rename 'Windows' to 'MS Win' (or whatever) and not update the code accordingly –  CrazyHorse Sep 28 '12 at 7:51

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());
}
share|improve this answer
1  
you just duplicated windows instead of an id. –  Ryathal Sep 27 '12 at 17:42
    
The first point "retrieve id based on system name" would push the issue from the ID to the name value. But I like the idea of a dedicated query for the windows filter. Something has to give when the filter is explicit at compile time vs dynamic. –  mike30 Sep 27 '12 at 18:51
    
In an actual system he probably wouldn't use "Windows" either. First he'd query all systems to display to the user. Behind the scenes, the program would remember the ID of each system so that when the user picks one, the program will already have the system ID for a SQL query, or to grab the appropriate object with Hibernate as in System chosenSys = HibernateUtil.get(System.class, systemId); –  GlenPeterson Sep 27 '12 at 19:01
    
@GlenPeterson. I agree with having a dynamic filter value chosen at runtime. I think your dedicated query is the best answer so far to address a static filter value. I'll upvote but I need to create an account. –  mike30 Sep 27 '12 at 19:20

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.

share|improve this answer

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.

share|improve this answer
    
Indeed, querying the database is best. Queries have the lowest possibility of returning stale or incorrect data. But you wouldn't normally add an entry once or twice a year to an enum in code. Instead, you'd build the enum at compile time by querying the database. Building enum code from the database avoids a host of errors. –  Mike Sherrill 'Cat Recall' Sep 27 '12 at 14:50
    
@Catcall, this way is a lot of work and very inefficient. Every time the database is updated, you have to recompile and redeploy. If you just query the properly, you don't have to change the software when a new ID is added. –  Tyanna Sep 27 '12 at 15:39
    
@Tyanna: Querying the database is best, which is what I said. But there are use cases where it actually makes sense to use an enum. In those cases, building enum declarations from code that queries a database is almost always better than manually maintaining the enum. –  Mike Sherrill 'Cat Recall' Sep 27 '12 at 16:38
    
I do like this suggestion, as in all probability there would be few enough systems requiring explicit reference in the code that we could have got away it, and all would have been fine. However, in the spirit of conforming to best practice we have decided to go with the Dictionary approach –  CrazyHorse Sep 28 '12 at 7:54

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.