Adding a Code Table

A technique that adds a lot of "power" to your database is to create a table specifically to manage global constants and variables in your database. Note that it will not have any Key "Relationships" to any tables.

Constants

Many databases are created for a specific client, and in order to customise it for a customer you can add their name (and address) to every form and report. However, what if you want to use the same database for another client. This would mean you would have to change the name on every form and report in your database. A better way is to create a control table and add the Client name (and any other details like address, phone) then for the forms and reports in the database use this instead of hard coding.

A code table (tblCode) structure could be

CodeGp, Code, Description, Value

A data dictionary for the table would be.

where the PK is CodeID+Code, and value is added if you want to have a Long Integer. As you understand how useful the control table can be you may add additional attributes.

So you can add the client name to If you want to create a database that you can

Variables

As you will realise some attributes can hold specific values, e.g. Salute can be Mr, Mrs, Ms, Dr. This can be hard coded into the table when you create it, but it would be more powerful if you could just add the valid values into the control table and when the user drops down the list of valid values it selects from the Control table.

Hint

If you have a selection it can be useful to have an Other or Not Applicable (N/A) option in case there is a privacy issue or the user does not want this data to be collected.