>One more thing, although you probably know this. It is worth-while to think this over well, and do some tests, better for a few weeks than for a few days. If you decide to change the structure after you program for a year, you will lose much more time.

Agree. We are just at the right moment for that.


>"The PK is the field or fields that uniquely..." etc., but I prefer the singular version. >With two fields, both the permanent relations and the joins become quite complicated - and >probably less efficient, too.

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.

At this time we are planning to write the middle tier as well as the LAN interface with VFP and using ASP.NET for internet access, so VFP remote views can also play a part.

>That leaves three options that I am aware of.
>
>1) GUID, or similar algorithm that practically guarantees a unique value anywhere in the >world. The only disadvantage is the size. If the PK is big, the FK in all other tables is >just as big.
>
>2) Reserving ranges of numbers for each branch (school). The problem here is that it is hard >to guess how much each table will grow.
>
>3) My suggestion above. 2 bytes for the school leaves room for 65,000 schools; for bytes for >the serial number allows 2e9 records per table.
>
>This last solution seems ideal to me, but I don't have personal experience. And there may be >other possibilities I am overlooking.
>

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.

Would welcome your comments. Gracias.

Alex