Composite key for a table of system constants

[AndrewMozley]

I have an application which has a PARAMETER table in which I save several interesting “System constants” which the user may set and which determine some aspects of the running of the system

The structure of the table in slightly simplified form is that there is a Code field, a Description field and a Data field. So I might have

Code.......Desc..................................Data
SYSCO....Name of the company.........The Arms Trading Company
INVPRE...Prefix for sales invoices.......IN
VAT1.......Standard VAT rate.............20.0%​

Some of these are “one-off” items, as for example the name of the company, but some occur in groups - for example the user might decide to have identifying prefixes for several sorts of documents, or the system might need to know what codes are used for several control accounts in the accounting nominal ledger.

I had therefore thought of including in the table a 2-character code, “Group” which could be used to group together all the VAT codes and all the control accounts &c
So in this case the unique identifier of a record would be the combination of the “Group” and the “Code”.

My slight problem is that composite indexes do not appear to be encouraged in VFP. I have been considering various solutions, like including a separate field “Group_Code” which would be that unique key, but then I would need to populate that field when I add a new record - I could not define it as a bound control (not even as a disabled or hidden bound control)

Grateful for any ideas.

 

[craigber]

Use of composite keys has been a big debate for years, not just in the VFP community. Personally, I only use them for many-to-many tables. The thing to keep in mind is the key is only used internally by the system. It's not something the user will see (again, this is up for debate). I see nothing wrong with what you want to do.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

You can index on any expression, so the simplest way of a composite index would be to index Code+Group instead of Code,Group.

Bye, Olaf.

 

[Mike Lewis]

Andrew,

I happen to know that most of the tables in your application have an integer primary key (which happens to be named ID). These are all surrogate keys, that is, they do not have any specific meaning beyond their use as primary keys.

For the sake of consistency, I suggest you do the same thing with your parameter table. That doesn't stop you from creating indexes on the group and the code - as separate indexes - to speed up searches (on the other hand, if the table is likely to be very small, you might consider not bothering to have any indexes at all apart from the primary key).

As far as your control accounts are concerned, have you considered an alternative approach: you presumably have a table of nominal account codes; why not include a flag in that table to indicate if a given account is a control account? Just a thought; I don't know enough about your tables to know if that makes sense or not. But in any case it doesn't affect the main question.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads