Challenge - Keep Code and DB Rows Separate

Question

We all agree that the db is for storage and that the code shouldn't be tightly coupled with it. How do you achieve this when you store preferences in the db. Let me give an example.

Suppose that you are creating a little online poll. Each poll can have a "vote type". Here are some vote types:

• Anonymous Voting
• Ip based voting
• etc.

Now if there is an interface where a user creates a poll and stores the "vote type", it seems inevitable that at some point you associate something in the db (either the primary key or a name) with the code that does 'anonymous voting' 'ip based voting' etc.

In a different, but related question, John Sanders said that this is silly - and I do agree completely with him! Why connect db and code - it is a bad idea. But how do you avoid it if you are storing something like "vote type"?

Answer 1

Correct me if I'm wrong, but can't you just store a vote_type field in your Polls table? If the vote_type is ip, you use the storeVoteByIP() function. If it's userID, you use the storeVoteByID() method - or one method with a switch.

Perhaps I'm just naive, but I don't see the challenge here :)

switch($vote_type) {
  case 1:
    #insert_vote_by_ip();
    break;
  case 2:
    #insert_vote_by_userid();
    break;
  ...
}

What am I missing?

Answer 2

One thing to do would be to just store the preferences as XML into an XML column in the database. Then the database doesn't care what gets stored - it's up to the code to figure it out.

Answer 3

There needs to be some connection between your code's data structures to the structure of the database. Trying to avoid this for the sake of not mixing application code and data code is not worth the effort - you would need to design both your code and data in the most general possible way. This might be necessary for some applications (design an application that lets users design data structures), but is likely not what you want for many others - Do you really want to have the query the database to learn how your voting tables are structured? how many columns each has? what the names of the columns are?.

A good way to go about maintaining a neat and organized application is to maintain a component from which both the table and the application code draw their values.

So, for your example, you could design a VoteTypes class with some static final fields. To access the names of the values that actually go into the database, your code would call

getAnonVotingTypeName() getIpBasedVotingTypeName() etc..

This way if the names ever changed, you would only need to change them in that one class (and update existing records in the tables, of course).

Answer 4

You could have a enum. In C#, something like:

public enum VoteType
{
    ANONYMOUS,
    IP_BASED,
    OTHER
}

and then, in the code you simply say something like

switch(vote.Category)
{
   case VoteType.ANONYMOUS: //do stuff
   case VoteType.IP_BASED: //do some other stuff
   //etc
}

This is better than using a string based, it avoids some errors of mispelling and stuff like that.

Answer 5

The main reason for decoupling code and data is that at some point you may want to present the data in a different way. At that point, if you have tightly coupled code and data you will have to do a lot of work to strip it out.

Another reason is that it is hard to beat the ease of making code changes in text files rather than the database.

So, if you are sure the presentations will never change, and you know of an easy way to keep your code up to date, there really is not big reason to keep them separate.

However, It's very rare to see a case where this is the case.

Answer 6

Storing preferences/configurations can be a good idea if they change very oftenly, like if you create new voting types with a good frequency.

By the other side, if the new voting types are very important to your model (like when Ip based voting have a whole system to get statistics, or a lot of business logic associated to it), they have some right to be promoted to entities, classes, etc.

But I don't think you can decouple totally the database from your code, just because the database have influence in your source code(especially when you work with transactions).

Answer 7

Just define some constants, whether they're in an enum or whatever your language supports, e.g.

enum VoteType { Anon = 1, IP = 2, ... }

Have a vote type column in the Poll table in database with the constant value in. If you want to enforce referential integrity in your database then create a lookup table with the relevant value, otherwise don't, in which case these are just an opaque piece of data to the database.

There's no more coupling here than the Poll table having a column for, say, title. It's a facet of the poll you need to store, so just store it.

Answer 8

Let's suppose that you create a class that actually does the hard work of figuring out what VoteType should do (so it can check things like is_another_vote_available) etc. So you could have:

• VoteTypeIpSingleVote: this one allows one vote per ip address
• VoteTypeAnonymous: this one has no restriction on voting.

In the db you would have entries in the VoteType table:

• 1 Single Ip Voting
• 2 Anonymous

You could then make an interface which all VoterTypes (VoteTypeAnonymous etc.) implement that has a property "VoteType" which returns either

a) the primary key of the row they are associated with b) an enumerated type whose hash is the primary key c) the primary key which is found by doing a lookup in a settings table (this has the advantage that the connection between the database and code is accessible to everyone)

The advantage of this approach is that you can have a manager class which each of the VoterTypes register with (or could even be automatically registered with using reflection).

So you could call:

id = fetchVoteTypeToBeUsedFromDb

manager.getVoteType( id ).is_available(datetime.now())

And that way new VoteTypes could be seamlessly added just by registering themselves and not having to continaully update a switch statement.