Global Variable or lookup table 1

[danwand]

Hi there,

I am wondering what would be best practise in the following circumstances:


I have a client benefits table that amongst over fields contains information about whether the client recieves a certain benefit (benefit recieved: yes/no), if yes then i need to show the benefit amount.

Now this amount will be the same for every client, but may change over time (the change will be the same for all clients).

The question is should this benefit amount be stored in a separate linked look up table; as a global variable (that can be changed when neccessary); or even within the client benefits table (by implementing a function to change each benefit value for all clients?)


Any advice would be appreciated,
Dan.

 

[Crowley16]

definitely not for the global variable, as changing these will require you to modify the code.

as to the other options, it really depends on how you see the field being used in the future...

if it's always going to be a single value for all patients, then a lookup table would be fine, you can even incude date fields to audit changes if necessary.
however if you think that at some point you will be required to have exceptions to the rule, then storing it against individual clients would make sense.

there is another more flexible option which would be to create the lookup table, and then have another link table to specify which customers are on what benefit. However this is more complicated to implement and you need to think wether it's necessary to normalise things that far.

--------------------
Procrastinate Now!

 

[danwand]

Thanks Crowley,
that was just the type of confirmation i needed. I will go for the lookup table option. My initial thoughts were that because i already have a large number of tables/relationships/indexes in this database was it wise to create another table for just this simple purpose, but i guess this is the most logical way of doing things.

Thanks again.
Dan.