Need design suggestions for indexed-no duplicates field

[jax1000rr]

Hi, I am working on a new DB and running into an issue with an archive table I have. The DB is currently setup to not allow duplicates of the field "file_number" on table t_filetrac. The data is imported from an Excel file into a table named t_uploads first, then appended to t_filetrac. I import to t_uploads first so I can run some queries to edit certain data before the record is inserted into t_filetrac table.



I have set up a table named t_archives which I want the end user to be able to select a date range and append loans from t_filetrac from a specific date range (t_filetrac will quickly build up to over 10K records so I want to archive some records to allow for faster DB operations).

I need to have the append query from t_uploads to t_filetrac also check t_archives to ensure that the "file_number" is not present and if it is, do not append to the t_filetrac again.

Suggestions?

[rpeare]

You shouldn't need to 'archive' as you call it. 10k records is nothing and you will not see any noticeable difference in your data access times. Your better option is to limit the data you see by date rather than trying to move all data that doesn't fit a specific date range into a secondary table.

[jax1000rr]

Thanks, in your opinion, how many records can access hold before it will get bogged down?

[rpeare]

If you are not doing anything super complicated in your queries it can be quite large.

I've currently got a database with 300,000+ records with 160ish fields that I have no problems with whatsoever.