>About using single field PK's. In general that seems simpler, although adding school and period to the query seems more intuitive.
>
>Talking about speed. I will likely do a lot of filtering by school and period. Do you have experience with SQL Server views? Perhaps I can do the filtering at the Server View level. Do they harm speed much vs selects with extra conditions? Are they updateable? How about refreshing them if several users are capturing data.
Supposing you have the combined key in a single field, one possibility here is to have one field as the combined PK (school + serial number), and another, separate, field as the school, at least for some tables.
Another possibility, if the school appears first, is to filter by
PK = bintoc(lnSchool, 2), with
SET EXACT OFF.
>Please look at
http://fox.wikis.com/wc.dll?Wiki~HighLowKeyGeneration. That method is slightly different than your suggestion, but it allows taking advantage of identity columns, which simplify things. I did tests and think it would work.
If I understand the Wiki correctly, that would assume you have an actual connection to a central server. In my previous suggestions, I was assuming that much of the time, you can't have this connections. This, of course, changes the situation quite a bit.
The high/low approach seems adequate to reduce Internet traffic, which is what you want to do, if I understand correctly. On a LAN, this wouldn't be so relevant.
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)