Very large tables - divide them?

[leicklda]

I am new to Access ...
I am planning a database project with about 1,000 records (an inventory log of books). Putting 1,000 records in one table doesn't seem right. I'd like to divide them into smaller tables (like one for children's books, one for mysteries, etc...) just so that I don't have a table with an unmanageable amount of records in it.

But if I divide them, then each table will have practically the same field sets - name, date, publisher, author, etc... And I run into problems with relationship settings and data retrieval when I have different tables with the same fields in them.

What fundamental concept am I missing here?
Thanks!

 

[930driver]

1000 records is quite small by most standards, don't worry about that aspect.

Leave your records in the same table. As you surmise, if you split them out you will have reporting problems in the future. You should consider adding a 'Type' field to specify whether it is a mystery, children's, what-have-you. You can use a lookup table and just store the key value in your Books table. When you have your Type field in place it is simple to query just for Children's and display a managable sub-set of your whole table.

What ever you do, don't break up your books table! at least not for the reason that you are talking about. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[leicklda]

Wow 1,000 records is small? That helps a lot, and makes me feel better if maybe that number will be doubled or even tripled in the future.

I have to get used to the world of big databases I guess, and not just tracking small inventories of things.

So when tables get very large, say many thousands of records, I guess you mainly interact with them with queries, filters, forms, etc, and just let the table be a storage place you don't really have to enter, so you're not overwhelmed by the massive amount of data in it?

Thanks!

 

[SgtJarrow]

That is correct. I have a couple of databases that grow by a THOUSAND or so each week....Not even an issue for the system. Then every few months, I archive a bunch of the older data as it is not necessarily needed all the time. But I have used databases in excess of 20,000 records before I experienced any real problems.

Once the tables are established, you should have little interacion with them directly. They should be accessed through your queries and forms. Just they way things work.

Good Luck! %-) Robert L. Johnson III, A+, Network+
robert.l.johnson.iii@citi.com
Access Database Designer/Administrator