Database with 5 million records

[thegooser]

Hi all,

I am about to embark on a bit of a project and think I may need to get a bit of advise before I do. There will be about 5 million records altogether so I think I need to plan ahead.

I will generate csv files containing about 20,000 records a time. I need add these to the database and filter out duplicates.

One of the fields will be a UK phone number. Using this phone number I can generate 3 extra fields "Country", "County" and "Town" which I will want to be able to search and filter in the end table. Am I better off to make a make table to import with the added fields, or shall I set a relationship to the final Access table and the locations?

Also, there will be a need to take out the duplicates. If I have 4 and a half million records in the final table and then add a csv of 20,000 records and then try to remove duplicates, will that be an issue?

And finally, how practicle is a table of 5 million records? Each record has about 10 text fields so it won't be a small database. Would it be better to split it into say 20 databases and query them all?

Any help or general advise would be great, I am just a little worried about the size of the database.

Thanks

 

[gemma-the-husky]

back to square one, i think

why 5m records

5m records of what?

you MUST get your table structure sorted first. ignore the town/county but of a UK phone number, That is pretty trivial. separate the phone numbers into area prefix and actual phone number.

 

[DavidAtWork]

I don't know your setup but with that many records you may want to consider a SQLserver database backend, you can still build an Access front-end on that no problem.

David