Should I use enum or query a table in my database?

Question

In my database I have tables that define types for example

Table: Publication Types

ID | Type
----------
1  | Article
2  | Abstract
3  | Book
....

Which is related through the ID key to a publication tables which has the field TypeID.

I then create a PublicationTable data table my .NET application which I want to filter based on the publication type. For example the following function gives me the number of publications for a specific author and publication type.

    Public Function countPublications(ByVal authorID As Integer, _ 
                                      ByVal publicationType As Integer) As Integer

        Dim authPubs As New PublicationsDataSet.tblPublicationsDataTable
        authPubs = Me.getAuthorsPublications(authorID)

        Dim dv As New DataView(authPubs)
        dv.RowFilter = "status='published' AND type='" + _ 
                       publicationType.ToString + "'"

        Return dv.Count

    End Function

To call this function to get a count of articles by an author of a specific type, I could

1. call the function with two integers

   countPublications(authorID, 1)

2. setup an enum so that I can write

   countPublications(authorID, pubType.Article)

   or

3. somehow use the publication type table to filter the publication data set but I haven't got my head around how to do this.

What other approaches should I consider.

Thanks

Answer 1

if publication types are essentially static, enums are fine

there is arguably little difference between embedding

inner join lookuptable lt on lt.id = (int)myenum.sometype

in a query and adding

inner join lookuptable lt on lt.name = "somehardcodeddescription"

they're both embedded constants, the former just has a well-defined type behind it

alternately you could use

inner join lookuptable lt on lt.name = myenum.sometype.ToString

i prefer the former

if, on the other hand, new lookup types may be added after the code is deployed, then an enum will quickly become outdated;

but if there is core set of static enum values that the code needs and the rest don't matter then the former solution is still fine

as usual, "it depends" ;-)

Answer 2

Having maintained this sort of thing in a previous life, I agree with Steven that an enum is quite reasonable. Your code is clear, and an enum means you need to update only a single file if you add data types.

I'd also suggest commenting the enum, making it clear that the values need to match those in the Publication Types table in your database.

Good question, by the way! +1 for explaining the question so clearly and taking the time to brainstorm solutions before posting.

Answer 3

I think it depends on how often your list of publication types will be changing in the future, and on how easily you can push out an update of your application. If the list won't change often, or if updating your app in the field is easy, then an enum makes sense. If the list is likely to change frequently, or if updating your app is particularly difficult, then keeping the list in a table in the database is sensible.

Answer 4

For various reasons it would be nice to keep lists such as my publication type list and others in one place; the database. Then there is only one place for them to change. However, it seems to me that this adds some complexity to the code and I would still need to have some hard coded elements in the code if I wanted to refer to a specific publication type such as Journal Articles. Therefore, having an enumerated type that reflects the data in the table gives me the possibility of calling my count function in a readable manner

countPublications(authorID, publicationType.JournalArticle)

If the data in the table changes which is unlikely, I can have a comment in the database to remind the maintainer (probably me) to update the enumerated type in the code and vice versa.

Thank you all for your answers. I can now proceed with my mind at ease.