data normalization & Excel; variable naming rules

[terrahawk]

Hi, I had a couple questions on normalized databases and variable names and would appreciate your feedback.

Question 1) We are looking to normalize our data structure but I am wondering if it would still be possible to deliver the data in an Excel format to our customers then? Currently, we provide customers (corporate) with Access & Excel databases but a lot of them do not know Access (or have it). For example, currently we provide 3 periods of data for several variables and our current variable names have the dates in them. If we normalize our databases, we should remove the dates from the variable names and put this data in a seperate table (not very Excel friendly then).

Question 2) For a long time, we have restricted variable names to 8 characters. We are thinking of making our variable names more user friendly so customers can understand them without reaching for the manual. Longer variable names are truncated when they are imported to some software (e.g., SPSS) but few of our customers do this. What sort of rules should we follow here for variable names? Is there an online reference you can point me too? The rules I was thinking of are:
- 25-30 character maximum
- no spaces or special characters except the underscore
- lowercase (or all uppercase? does it matter?)

Thanks,
Peter

 

[JoeEskimo]

Any normalized mutlitable data should be easily joined back together in a query and then can be exported as an excel file. The whole process can be automated without coding by building a macro in Access to export the query to an excel file on the network.

As far as naming structures, what you recommended is great..underscore and all. However, Access does provide a CAPTION for each field which is ver useful. Your field can be called AcctRecv but have a caption in Access of Accounts Receivable.

 

[harebrain]

For a long time, we have restricted variable names to 8 characters... Longer variable names are truncated when they are imported to some software (e.g., SPSS) but few of our customers do this.

If you have *any* customers who depend on such software, then your rules should include:

- first 8 characters must be a unique combination.

Wow, I just had a flashback to FORTRAN IV!

 

[terrahawk]

Thanks JoeEskimo & Harebrain.

 

[marklenel]

SPSS v12.0 now allows for long variable names and Upper/Lowercase lettering. (However - for SPSS users - this can make your syntax horribly complicated...)

 

[harebrain]

That's great, but until the entire user population upgrades to v12, it's no help to terrahawk. Ok, "entire" is an obvious overstatement that should read "at least his users." But then that puts an additional "minimal system configuration" requirement on the side of the box of his software.

 

[terrahawk]

Thanks, yeah I saw that SPSS update too Marklenel. I hope to purchase a few licenses this year so at least it will help us internally for our own analysis.

An update. We are starting the data normalization process and are going with a 32 character maximum (I think that is still a SAS limit). Otherwise, following some basic naming rules you guys probably already know.

Peter