>I developed this foxpro database that keeps track of students and what buildings they live in for my univeristy.
>
>In order to keep table sizes small etc... i have some tables that exist over multiple years (ex years, buildings) and some that get recreated every new year (ex students, movelogs). I do this by creating a new directory for the new year and creating the tables in that directory. This way to go back to an old year I just switch the directory path and reopen the tables.
>
>Now, im moving this to an sql server...
>
>Any ideas on how to implement this?
>I thought of two ways but cant decide on which one would be best.
>
>a) Since sql server is powerful enought I could just have one database with all the tables and create foreign keys in the year specific tables and filter for the current year. Only problem with this is that the tables could get HUGE.
>
>b) Or I could create different tables for the different years within the same database. ex student01, student02 and when I bring them down to my app i could just rename them student. Only problem with this is that there could be lots of tables in the database which are all the same, just different data.
>
>Let me know what you think?
>
>Thanks in advance, Eric

Usually, the second option is preferable even in FoxPro or Visual FoxPro. This simplifies maintenance greatly. Of course, available disk space, or the 2 GB limitation per file, can sometimes force you to do otherwise.

On SQL server, I would definitely recommend option "b".