Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    268

    Unanswered: Enumerations vs. Tables

    What are people thoughts on applications that are code heavy. Is it worth it to keep values in tables that can just as easily be stored in enumerated datatypes within the code.

    Example:

    pubic enum UserAccess
    Admin =1
    User = 2
    ViewOnly = 3
    end enum

    Or store this in a table with two fields Description and ID.

    Just wondering. Personally I think that storing the values of fairly finite values in enumerations is a simpler way to implement the code. Less hits to the DB for information, reducing overhead. However, this requires implicit knowledge of the DB if any new values may want to be added to the app in the future.

    What are some of you other VBA programmers doing?

  2. #2
    Join Date
    Jul 2003
    Posts
    73
    If the application has a significant number of different Key / Value pairs - I generally make a GenericKeys table as follows:

    Table GenericKeys:
    GenericKeyID (autonumber)
    TableName (name of the table - i.e. Title, UserAccess etc)
    Code (the code - i.e. Dr, Admin)
    Value (the value - i.e. Doctor, 1)
    Active (whether this code is active)
    <other kinds of information on the specific key>

    When populated it could look as follows:
    Code:
    ID  TableName  Code     Value
    1   Title      Dr       Doctor
    2   Title      Mr       Mister
    ...
    33  UserAccess Admin    1
    34  UserAccess User     2
    35  UserAccess ViewOnly 3
    This way you can have all of your key / value pairs in the one table, you can easily add to them at a later stage (especially when they are user maintainable) and can be used as follows:

    * In Combo Boxes just select all codes where TableName = 'UserAccess' and order by Code.
    * In tables (such as Employee) you only store the Key of the code (can actually save a lot of space if your values are much bigger than the key).
    * In SQL queries you can do:

    Code:
    SELECT a.EmpID, b.Value as 'Title', a.EmpName, c.Value as 'UserAccess'
    FROM Employee a, GenericKeys b, GenericKeys c
    WHERE a.EmpTitle = b.Code AND b.TableName = 'Title'
    AND a.EmpAccess = c.Code AND c.TableName = 'UserAccess'
    When I chose to allow the users to update these key values - you can add a wrapper table that stores the details of each table:

    Table GenericTable:
    GenericTableID (autonumber)
    TableName (name of the table - i.e. Title, UserAccess etc)
    UserMaintainable (whether the user can update values)
    CodeLength (the maximum code length)
    ValueLength (the maximum value length)
    <other kinds of information on the specific table>

    IMO - if your keys are very static - use Enums. But if you (or the users) want the freedom to update the keys / descriptions - a table isn't too much overhead.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •