>I have a table that needs to hold up to 5 8-char references.
>
>I was thinking of creating a 40 character field, and hold the references in there, but is there a better way of doing this, should I use 5 fields perhaps?
>
>Thanks
>Kev
For my £0.02 worth,
If you have 'up to' five additional fields, you should use a related table (normalisation and all that) to hold one record for each of the possible alternate ids. Otherwise you'll end up doing:
SELECT * from MyTable WHERE cKey1 = lcKey or cKey2 = lcKey or cKey3 = lcKey or cKey4 = lcKey or cKey5 = lcKey
Then when you add another possible key, you'll need to change all your code to check cKey6 also.
INSTEAD you get:
Table - MyTable
nId
Other Fields
Table - MyKeys
nId
nMyTableId
cKey
SELECT MyTable.*
FROM MyKeys LEFT JOIN MyTable ON MyKeys.nTableId = MyTable.nId
WHERE MyKeys.cKey = lcKey