Constants Table

[Sketchin]

Hey everyone,

I am writing a database that tracks inventory, Contacts, Inventory reservation and invoicing in separate tables.

Each entry in these tables can be in different categories, and sometimes multiple categories. (I.E different types of contacts and different types of inventory)

Is it good practice to have one table dedicated to fields that will remain fairly constant, but do have the possibility of being updated in the future? Things like countries, states/provinces, inventory categories, contact categories and payment types. Or would it be better to have a bunch of small tables with a Autonumber ID and a "constant name" field?

After writing this out, I feel that it may just be personal preference as to how you do it.

Thanks for any advice.

 

[gemma-the-husky]

each of those should be a separate table. i think.

a "constants" table general deals with various fields that can only take a single value -so a constants table only has a single row.

eg current tax rate, company name, address, phone number etc.

 

[MarkK]

In this conversation it might be good, too, to mention enumerated types. To declare one you can do something like...

public enum enEquipmentType
  enEquipNoType
  enEquipStatic
  enEquipPortable
  enEquipHandHeld
  enEquipGasPowered
end enum

Then Access automatically assigns values and you can use those identifiers in your code, so you could do...

  dim i as enEquipmentType
  for i = enEquipStatic to enEquipGasPowered
    debug.print i
  next

...so this way you can create lists of types and then declare variables of those types. Also, when you assign values you even get intellisense providing you with the list, so in this situation ...

   Dim tmp as enEquipmentType
   tmp = [COLOR="Green"]'when I hit '=' a list of options pops up[/COLOR]

An advantage is your code is more readable, understandable, and so on, but a disadvantage is that you essentally 'hard-code' the values.

But it seems worth knowing that you can do this.

Cheers,
Mark

 

[nanscombe]

In the past I have used, and in fact I still do, a single table to hold my lookup data.

Something along the lines of:

ID - Autonumber (Holds unique identifier)
Type - Text (Holds type of data, i.e. Country, County / State, Equipment Type, Contact Type, Address type etc)
strValue01 - Text (Optionally holds a string value)
strValue02 - Text (Optionally holds a string value)
lngValue01 - Number (Optionally holds a long value)
lngParentID - Number (Optionally holds the ID of a parent record)
Deleted - Boolean (Tick this if it is no longer required)
Datestamp - DateTime (Used in conjunction with the ID to update records across different databases)

It may not be the most efficient way to hold the records but I find it convenient.

The Fieldnames are not useful but I just use a query with an Alias on the field to get at the data, e.g.

luCountries

Select ID, strValue01 AS 'Country' from tblParameters Where Type='Country' AND Deleted = false ORDER BY 2

or

luCounties

Select ID, strValue01 AS 'County', lngParentID as 'Country' from tblParameters Where Type='County' AND Deleted = false  ORDER BY 2

If I need another type of data I just add a new value in the Type field.