Advice on database design 1

[jallen919]

I will be importing approx. 20,000 rows per month (info tracked is basic indinvidual info (name, address, phone#).

For every 20,000 rows only approx. 5% of those records will be used on a semi-regular basis. In addition, more info will be tracked (business phone, occupation, etc.).

From out of the 5% of records, a certain percent will turn into actual full time clients and a full client record will need to be created (income, assets, etc.).

So what I am trying to figure out is how the database should be designed. Should each group be tracked in separate tables, the first group in one table and the last two groups in another table with just a different status? Any help here is greatly appreciated. If there are websites out there that you think might help send them along -- please send anything along

 

[ghubbell]

Hi jallen,
Due to the volume you would be dealing with I would recommend holding the incoming in one table. Once designated as part of the 5%, shift them to another. In your next cut, shift again. Why? at 20,000/month you'll bog down your search speed and database performance tremendously if you start having to work with 240,000 records per year. I have seen systems that will "hold" seperate tables of the "rough cut" by month, sometimes in seperate linked databases. After a time the tables are discarded or archived.
There's little point in making work any harder than it need be, both for yourself and your database.
Please let this thread sit here for a while and see some others opinions too before commiting to something. You have a very interesting situation! Gord
ghubbell@total.net

 

[Terpsfan]

I'd set up an archived table with exactly the same number of fields as the main table you have set up for customer information. It's pretty easy to program a job to do this and automate it. After a certain amount of time I'd delete the records that won't be accessed on a regular basis and append them to a table of archived data to be referenced as needed. This way you keep the recordset down considerably and the forms will run faster. Another option is to retain all historical data in that table and base forms on a query that filters out the unnecessary data.

 

[Garyeag]

Have you tried "SearchDatabase.com".

I'm just returning to databases myself after a break of quite a few years so I'm learning a lot again. Things have moved on.

The above site e-mails you every morning with some very useful tips, industry news. I've found it as invaluable as this site only a little more "professional". They often have seminars live etc.

Worth checking out.